Richard Schollar: VLOOKUP Left!

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: