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

Formula Forensics No. 028 – It’s Just a Jump to the Left:
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.