Menu
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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Show Decimal Points if needed [Quick Tip] | A Spreadsheet walks in to a bar … [open mic] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
43 Responses to “Formula Forensics No. 028 – It’s Just a Jump to the Left”
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
Great Stuff guys! Didn't know this technique and it is really awesome!
Cheers, Lukas
(ExcelNova.org)
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.
The most important argument in the use of lookup is the FALSE at then end.
Your series didn't speak enough to its relevancy.
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.
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.
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.
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.
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.
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.
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 ...
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
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.
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.
That's it Hui! Congrats (:
Well said. A great thought-provoking idea in the first place. Thanks.
Awesome! Hui!!!
Well said, This is definitely a good post to bookmark. I am sharing it to my viewers too.
Good, I was waiting for clarification of this formula.
Excel Ninja SirJB clarify this in the comment :
http://chandoo.org/forums/topic/vlookup-9
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
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.
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
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.
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
...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 !!
This is simply great....wonderful....I can construct a matriz in my order....thanks
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
I wonder why Excel doesn't just use negative column numbers for looking to the left (or up, for HLOOKUP)...???
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…
@Jeff: you sir, are a proverbial genius. I have read some of your comments in other posts within forums surrounding Excel, and they are always so well written and explained (and error/typo free).
You are someone who we can all learn from. Thanks for taking the time to write meaningful and considered responses.
At last...someone who appreciates me for the genius I am. 😉
Thanks Pratish.
Jeff, there are many fans for you because your thoughts are funny and your tips are outstanding!!!
how to make my vlookup pick both letter and numbers in a cell.
eg B50.9
@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
Thanks Hui,
I'm glad I've found this. It works like a charm! Normally I have to copy data to the right, and start normal VLookup.
By inserting Choose {Table Array}, I can also select the fields I need to list in the Vlookup formula instead of having to list multiple columns and have to remember which column index number contains the data I want to return (it was a hassle in case some columns in the table were hidden)
Before saying to thanks I like to share experience..
Since few months back I was trying and making dash board along with staging table... With using conditional formulas which help me out to extract flat and tabular raw data form... And now little expertise on way which is going on...
The October month is very tough and crucial for me forv developing new dash boards... New data form...still now struggling.... And thanks CHANDOO ORG. to make my days happy without stress full coz of the new formulated combination of vlookup and choose formula...
And i have learn lot of things in that period if things newly try it... Some times will not work... But we get the ideas where we are what we have done...
Once again thanks' to CHANDOO ORG. To made my days happily..
And now will be going on short vacation post completing my work.
Heartily thanks
Keep teaching us! Best of luck ????
Before saying to thanks I like to share experience..
Since few months back I was trying and making dash board along with staging table... With using conditional formulas which help me out to extract flat and tabular raw data form... And now little expertise on way which is going on...
The October month is very tough and crucial for me forv developing new dash boards... New data form...still now struggling.... And thanks CHANDOO ORG. to make my days happy without stress full coz of the new formulated combination of vlookup and choose formula...
And i have learn lot of things in that period if things newly try it... Some times will not work... But we get the ideas where we are what we have done...
Once again thanks' to CHANDOO ORG. To made my days happily..
And now will be going on short vacation post completing my work.
Heartily thanks
Keep teaching us! Best of luck ????
I have always found that it is better to keep things simple. I usually just employ a hidden helper column that pulls over the lookup range.
Hi I find your data useful but need help with the following please. My excel is rather rusty as i have not used it in about 4 years and for the life of me cannot remember how to do the following:
this is the formula i am using at the moment and it works provided there is data for both but if there is data missing in the one it either n/a or it is blank if i use an If iserror
=VLOOKUP(R8,'WORKING do not DELETE'!B:F,5,FALSE)&" & "&(VLOOKUP(S8,'WORKING do not DELETE'!B:F,5,FALSE))
I am not adding up numbers but i need the text to look up the name in one cell and then look up the next cell and add them together if data found. but if data only found in one cell it and not the other it needs to only put in the one and not n/a on me.
sorry my explanation sucks. hope you can help me
Hi There -
Great article. Quick Question. How do I perform the reverse lookup when the table is in a different tab. Do I define the table before or after Choose. Thank you so much for your help!
Hi There,
I am stuck in getting the data from right to left.
e h m names
12 15 98 a
74 84 32 b
33 45 69 c
21 59 77 d
names h e m
a
b
c
d
In there respective coloumn, can you guys please help me in this.
Thanks,
Rahul
@Rahul
Can you please ask the question in the Chandoo.org Forums and pls attach a sample file
http://forum.chandoo.org/
No Zoolander reference...??
[…] ]3] https://chandoo.org/wp/2012/09/06/formula-forensics-no-028/ […]