Formula Forensics No. 028 – It’s Just a Jump to the Left

Posted on September 6th, 2012 in Formula Forensics , Huis , Posts by Hui , Random - 28 comments

In 2010 Chandoo wrote a post about options to perform a VLookup to the left of the Reference Column

Mike one of Chandoo.org’s astute readers had noticed a post by DaddyLongLegs over at the Excel Forum which describes a technique for using VLookup to look to the left of your reference column. Mike made a comment to the effect at Mike’s Comment

Today at Formula Forensics we have a look at this technique and see why it works as well as looking at some extensions using the same idea.

As always at Formula Forensics you can follow along with a sample file here: Download 97-2013

 

VLOOKUP

Lets start with understanding what the Excel VLookup() function does.

VLookup uses the syntax:

So VLookup looks up a Lookup_Value in the first Column of the Table_Array and returns a matching value from the same position from another column in position Col_Index_No of the Table_Array.

Of note here is that the Table_Array is specified for the lookup area, not a Range.

A Table_Array can be a Range as specified in the Syntax above eg: A2:D8 or it can be a Named Formula or it can be a formula that returns a Range as a solution.

We can use this to trick Excel into accepting an Array which has Column 1 to the right of Column 2. Effectively meaning we are returning a value from the left of Column 1.

 

Mike’s Solution

Lets look at Chandoo’s first Question: Which person made sales = 1088?

Mike supplied the solution:

=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)

=John

Which we can manually see is correct and the answer is in fact to the left of the Lookup value of 1088.

Mikes formula: =VLOOKUP(1088, CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17), 2, 0)

Is a standard VLookup with:

Lookup_Value: 1088

Table_Array: CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17)

Col_Index_No: 2

Range_Lookup: 0

So we can read this as lookup the value 1088 in Column 1 of the Table_Array and return the equivalent value from Column 2 of the Table_Array.

But what’s this Table_Array of: CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17) doing?

In a Blank cell say I19 enter: =CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17) press F9 not Enter

Excel responds with: ={1592,”Joseph”;1088,”John”;1680,”Josh”;2133,”Jamie”;1610,”Jackie”;1540,”Johnson”;1316,”Jonathan”;1799,”Jagjit”;1624,”Jairam”;726,”Jessy”;2277,”Javed”;714,”Jimmy”;2682,”Juno”}

 

We can see this is an array of the elements from Column B and Column D

The 1592 is the first value in Column D, and Joseph is the first value in Column B

Then 1088 is the second value in Column D and John is the second value in Column B

Then 1680 is the third value in Column D and Josh is the third value in Column B, etc

You can see that Excel uses the “,” to separate entries in different columns in the same row and then uses “;” to separate the different rows

So the Formula =CHOOSE({2,1}, $B$5:$B$17, $D$5:$D$17)

Has setup an array where Column 1 is Range D5:D17 and Column 2 is Range B5:B17

Back to VLookup

VLookup looks up the Lookup_Value from Column 1 of the Table array in this case we saw above that this is the Range: $D$5:$D$17

Vlookup finds the position of the Lookup value, 1088, in our case is position No 2. And the goes to Column 2, which is $B$5:$B$17 and returns the value from position 2 which is John.

 

Why has Mike Used {2,1} ?

Why has Mike Used {2,1} ?

As it turns out it doesn’t matter what order the array elements are listed as long as the Ranges listed in the Choose function match the array order

If Mike had used {1,2} instead he would be still able to rearrange the formula to make it work

=VLOOKUP(1088,CHOOSE({1,2},$D$5:$D$17, $B$5:$B$17),2,0)

Noting that Choose position 1 is still D5:D17 and Choose position 2 is still B5:B17

 

You can check that out for yourself at Cell I21

 

Extending this Technique

You can add any number of ranges of data to the Vlookup function by simply extending the Choose Function, ensuring that the Choose Array ranges matches the Ranges order in the Choose function.

So the following function will allow us to look up a value from Column D (Column 1) and return values from either Column B or C (Columns 2 & 3 respectively) by simply changing the Column_Index_No 3

=VLOOKUP(1088,CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17,$C5:C17),3,0)

You can see here that Both Lookup Columns are to the left of the Lookup Column.

There are a number of such samples in the Extension Questions and Solutions section in the example file.

 

Download

You can download a copy of the above file and follow along, Download Here – Excel 97-2013.

 

Formula Forensics “The Series”

This is the 28th post in the Formula Forensics series.

You can learn more about how to pull Excel Formulas apart in the following posts: Formula Forensic Series

 

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share like above, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

Your email address is safe with us. Our policies

Written by Hui...
Tags: ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

28 Responses to “Formula Forensics No. 028 – It’s Just a Jump to the Left”

  1. Saran says:

    Thanks Hui

    Well explained.

    This is the one of the best example article for bsic array formula learners.

    They can able to learn simple array as well as advanced vlookp.

    Regards,
    Saran
    http://www.lostinexcel.blogspot.com

  2. Lukas says:

    Great Stuff guys! Didn’t know this technique and it is really awesome!

    Cheers, Lukas
    (ExcelNova.org) 

  3. George says:

    Whilst this is very clever, why would you ever use it rather than an Offset() Match() combination?  They seem to be much more intuitive and significantly more flexible.

  4. Kevin says:

    The most important argument in the use of lookup is the FALSE at then end.

    Your series didn’t speak enough to its relevancy.

  5. David R says:

    I have to agree with George. The much more elegant solution would be to simply use INDEX and MATCH. Run and INDEX on the return column data (column B in this example) and then use MATCH to determine the row to return (column D in this example).

    An item to note: make sure to specify that you want an exact match when using the MATCH function by using a 0 as the third parameter in the function.

    • Kdu Bonalume says:

      I’d say that the beauty of Excel is that: each person can decide by itself which solution uses among the available possibilities.

      You may choose INDEX+MATCH, Hui would prefer VLOOKUP+CHOOSE and otherwise I’d try OFFSET+MATCH. 

      That’s the point. Excel has many different way to solve situations and them all make you learn more and then someday this knowledge will be applied in another problem, like “oh, that array formula using choose will be helpful now!”.

      Kdu.  

      • shrivallabha says:

        Kdu,
         
        While you can surely use OFFSET, it will be important to keep in mind that OFFSET is a volatile function and excessive use of it will slow down your sheet calculations. You can also try INDIRECT which belongs to the same volatile category but then you can refer any sheet from open workbooks to lookup.
        INDEX + MATCH is tried and tested solution.

        • Kdu Bonalume says:

          I’m not saying that i prefer OFFSET+MATCH, but I was just trying to show that all those ways are helpful and make us better prepared for new challenges at work and stuff.

          And obviously the main point was to present the vlookup with choose function. That’s why I think we should just talk about our own ways to solve it and fill this topic with good stuff for everybody as a “Search Book”.

          Kdu. 

          • David R says:

            I didn’t mean my response to cause any animosity. I tend to use large data sets within Excel with many functions, so whenever possible I like to use methods that help draw down the requirements on my computer’s memory and processor. I’ve found that the INDEX/MATCH combination works best given those parameters.

            That said, in small files with minimal amounts of data, I agree that any method that works should be considered correct as it usually doesn’t have a noticeable difference on overall worksheet efficiency and speed.

  6. Giles says:

    A third vote for George’s response… as soon as I started reading my thought was why do you even need this solution.  Index and Match (my preference) or Match and Offset accomplish this easily and elegantly and with much greater flexibility.

  7. Julien says:

    Wow, that looks very complicated compared to what I got:

    Column A is all my salesmen 
    Column B is all their sales values.

    =LOOKUP(<value>,B:B,A:A) 

    In the past, I also used INDEX and MATCH … 

    • Amar says:

      Hello Julien,

      For Lookup function the values in lookup_vector must be placed in ascending order, which sometimes may not be the case (or easy while handling large set of data).

      Regards
      Amar

  8. Kiev says:

    Even we can do the same job with different combination, but i think the main point for this post is learn more about Vlookup by utilize Choose function.

    For me, i like the Choose function instruction very much as i have never be clear after reading this post.

    Thank you.

  9. Hui says:

    Hi All
    Thanx for the feedback both positive and negative on the use of the Vlookup function to do searches to the left of the reference column.
    Formula Forensics started 28 posts ago, by answering a simple question as to how something worked.

    Formula Forensics continues along the lines of:
    1. How does a formula work
    2. What new or alternative techniques can we discuss
    3. What are alternative ways of tackling problems

    Formula Forensics has never said:
    1. This is the way you must do it
    2. This is the only way to do something

    Often in previous Formula Forensics we have seen dozens of alternative solutions posed in the comments, none are more right than any other solution, provided they return the correct answer.

    So, Please keep an open mind when reading the techniques we discuss at Formula Forensics. Add the techniques discussed to your Excel Toolkit and Excel Knowledge Base as you never know one day soon you may be confronted with a situation that demands a Choose({1,2}, …) solution and now at least you are prepared for it.
     

  10. Jitendra says:

    Good, I was waiting for clarification of this formula.
    Excel Ninja SirJB clarify this in the comment : 
    http://chandoo.org/forums/topic/vlookup-9
     

  11. SAUMYA UPADHYAY says:

    hii Chandoo,
    Thank for helping us,
     
    What if the data is on different sheets????
     
    Do we to consolidate in 1 sheet then only this formula will work??
     
    Regards.
    Saumya
     
     

  12. Jeff Weir says:

    Interesting use of CHOOSE. While INDEX/MATCH would be my first response, this is a timely reminder of the CHOOSE function, which  – while not my first choice here – can be the best approach to other problems.
    For instance,  you could use the ‘reference’ version of INDEX to dynamically sum a particular named range based on a number stored in A2, like this:
    =SUM(INDEX((Range1,Range2,Range3),,,A2) )
    …where Range1 etc are named ranges (consisting of one or more cells in each range) and in A2 is a number telling Excel which of those three range names you want the INDEX function to return
    For instance, say we have three named ranges: Sales, Forecast, and Variance. And lay we have a picklist in cell A1 where a user can choose either ‘Sales’ or ‘Forecast’ or ‘Variance’. And in A2 we have an IF or VLOOKUP or even CHOOSE function that returns 1 if the user selects Sales, 2 for Forecast, and 3 for Variance.
    Then we have a formula like this:
    =SUM(INDEX((Sales, Forecast,Variance),,,A2))
    …which dynamically returns the sum of the range that the user selects with the picklist.
    Choose can do the same thing with this formula:
    =SUM(CHOOSE(A2,Sales, Forecast,Variance)) as the CHOOSE function also accepts ranges.
    In fact the CHOOSE function can be better at this than INDEX, because it doesn’t care if your data ranges are on different sheets or even in the same workbook. Whereas try to point index to multiple ranges on different sheets and you will get an error.

  13. Danièle says:

    Thanks for a great “forensic” and a new array usage.

    I took a long time to use choose, but it is a very handy tool as Hui demonstrated here ( and I like this option a lot!)as well as Jeff Weir.
    It is lighter to use than index, and that is very appealing.
    I totally agree with Saran that to grasp the logic of array formulas, this one is ranking top for me.
    Thanks!

    Cheers,
    Danièle  

  14. Bryn says:

    A most interesting post; some things that are relevant to this and similar discussions.
    The most important equirementis an accurate answer; using data to enable decisions is better than faormula elegance.
    Spreadsheets will outlive our interest and someone else will have to pick up the tab(s). So simple formulas will save them hassle.
    Your time is valuable; a diect technique will use less of it. Also simple formula are reasy to debug and to prove.
    The value of this example to illustrate array processing has been understated. Arrays are not intuiive to most and we need effective examples.
    I find that many beginners struggle to get their heads around vlookup at all, so I now teach index/match and build form there.
    This formula is effective though, and just shows that dogma like ‘vlookup only looks to the right’ is rarely the last word.
     

  15. Sudhir says:

    Hi Chandooooooo…. this post was like WOW !

    I have been a great fan of your site since 2007 (one of the earliest ?)…thanks for the knowledge sharing !

    Sudhir  

    • Sudhir says:

      …also, I note that by using Choose, your data need not be continguous, you can actually pick up random three columns of data as you require, and build your vlookup !!

  16. alejandro says:

    This is simply great….wonderful….I can construct a matriz in my order….thanks

  17. Mayank says:

    Nice post…
    I am comfortable with macros but this formula is awesome
    I just hav a quick question
    If I want to introduce this as a new function in my excel sheet named let say
    VlookDown(value to be searched , column range to be searched, column range to be returned, rangeLookup)

    and then I can code this to use formula
    Vlookup(value to be searched, CHOOSE({2,1}, column range to be returned, column range to searched), 2, rangeLookup)

    I am not sure how to use formula in excel code..
    Please suggest the needful

  18. Dave says:

    I wonder why Excel doesn’t just use negative column numbers for looking to the left (or up, for HLOOKUP)…???

  19. Jeff Weir says:

    Dave – probably because of recalculation reasons.

    VLOOKUP makes you specify a table in which to do the lookup. The reason for this is so that Excel can monitor that table range, and only recalculate that VLOOKUP when anything in that table range changes (or any formulas upstream of that table range change). So change something outside that table, and Excel won’t recalculate that VLOOKUP. This makes it something called a non-Volatile function.

    If VLOOKUP allowed negative numbers, those numbers could result in references that fall outside the specified table range. They could fall anywhere on the entire sheet. So anytime you entered new data anywhere in the spreadsheet, Excel would have to recalculate the VLOOKUP too…because it couldn’t be sure that what you just changed in the sheet was referenced by the VLOOKUP or not.

    This would make it what’s called a Volatile function. If you have volatile functions in your workbook, any time you make a change anywhere at all on the spreadsheet, Excel recalculates the value of all those volatile functions too. Excel then recalculates every applicable formula downstream of these functions too – even though most probably nothing changed. Volatile functions include OFFSET, INDIRECT, RAND, NOW, TODAY, and my personal favorite, MEDICATION.

    More about this here:
    http://chandoo.org/wp/2013/09/29/i-said-your-spreadsheet-is-really-fat-not-real-phat

    …under the heading Handle sweaty Dynamite and Volatile Functions with extreme care…

  20. victor ahiale says:

    how to make my vlookup pick both letter and numbers in a cell.
    eg B50.9

    • Hui... says:

      @Victor
      =Vlookup(“B50.9″,Range, Column)
      eg: =Vlookup(“B50.9″,A2:B20, 2)
      or if B and 50.9 are in cells A1 and B1
      =Vlookup(A1&B1, A2:B20, 2)

      etc

Leave a Reply