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:

 

 

 

 

About these ads

About Bill Jelen

Author of 31 books about Microsoft Excel. View all posts by Bill Jelen

15 responses to “Richard Schollar: VLOOKUP Left!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 53 other followers

%d bloggers like this: