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:

LauraCK

Thanks a lot for this one, I have usually rearranged data:) this is a time saver!

Kip J.

Appears that VLOOKUP/CHOOSE is really six-to-one, half-dozen-to-another with regard to INDEX/MATCH … thanks for the tip!

Jennifer Deacon

Interesting method, I’d use INDEX/MATCH out of habit so will explore the CHOOSE function more, thank you.

Michael Blackman

Very clever Mr Schollar!

kaliman

Clever. Nice option. Have not heard about =Choose before.

K. NARAYAN

Wonderful. Thanks.

Pradeep

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

Michael Blackman

Hi Pradeep,

It’s not a CSE formula, the { are manually typed into the formula.

Pradeep

Thanks Michael….

Sahreesh

This is a lot simpler …thanks for the formula

Somaiah

Thank you very much…This awesome…Great work

Kamran Mumtaz

Awesome!

raj

Thanks, I always used index/match functions before; but I find it is easier to remember vlookup/choose method.

Get values to the left of VLOOKUP.

[...] 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 [...]

Alexis

Wow…this just saved me so much time…thank you!!