Richard Schollar provides this guest post. Richard is an Excel MVP from the U.K. When I saw the title of Richard’s post, I figured he was going to use INDEX/MATCH, but Richard really uses VLOOKUP!
How do you use a VLOOKUP if your table data holds the lookup value in a column to the right of the data column you want to return?
We’ve all had this problem – you want to use VLOOKUP but your data is ‘round the wrong way’:
You have the ID, you want to return the Description. A normal VLOOKUP won’t work as you can’t use a negative column:
One alternative is to use INDEX/MATCH e.g.:
But wouldn’t it be cool to be able to use VLOOKUP? But we can’t though, can we? Sure we can, if we make use of the CHOOSE function:
That formula in G2 is:
That formula is =VLOOKUP(F2,CHOOSE({1,2},$C$2:$C$7,$A$2:$A$7),2,False)
How it works: CHOOSE returns an array of two ‘columns’ in the right order, based on the order in which you pass the columns into the CHOOSE function. This creates an array that is in the correct Left-to-Right orientation for VLOOKUP to work. Here is the Evaluate Formula dialog after evaluating the CHOOSE function:
March 27th, 2012 at 13:37
Thanks a lot for this one, I have usually rearranged data:) this is a time saver!
March 27th, 2012 at 20:09
Appears that VLOOKUP/CHOOSE is really six-to-one, half-dozen-to-another with regard to INDEX/MATCH … thanks for the tip!
March 28th, 2012 at 09:30
Interesting method, I’d use INDEX/MATCH out of habit so will explore the CHOOSE function more, thank you.
March 29th, 2012 at 17:06
Very clever Mr Schollar!
April 2nd, 2012 at 15:16
Clever. Nice option. Have not heard about =Choose before.
April 4th, 2012 at 02:47
Wonderful. Thanks.
April 9th, 2012 at 01:42
Thanks for the tip,
I too usually rearranged the data. just wanted to know
Is it a array formula, do we have to enter it by pressing Ctrl-shift-enter ?
bcoz we can see curly braces after Choose formula
April 9th, 2012 at 18:04
Hi Pradeep,
It’s not a CSE formula, the { are manually typed into the formula.
May 15th, 2012 at 01:38
Thanks Michael….
June 15th, 2012 at 05:12
This is a lot simpler …thanks for the formula
July 30th, 2012 at 04:41
Thank you very much…This awesome…Great work
January 8th, 2013 at 12:33
Awesome! 🙂
February 26th, 2013 at 14:24
Thanks, I always used index/match functions before; but I find it is easier to remember vlookup/choose method.
March 19th, 2013 at 09:57
[…] I would use INDEX/MATCH as per Vog, but there are ways to actually use VLOOKUP to do this eg: Richard Schollar: VLOOKUP Left! | VLOOKUP WEEK […]
March 25th, 2013 at 15:40
Wow…this just saved me so much time…thank you!!