Reverse VLOOKUP


March 12th, 2013 by BenneTan BenneTan

I’m sure you’ve been told the lookup_value in the VLOOKUP formula must always be on the left & the data you require on the right.  So if I wanted to look for the profits for Company B in this scenario, it’s going to be easy:

Basically, I will look up for company B in the left most column of my table_array & return the 3rd column from it.    Here’s more on VLOOKUP:

http://www.advanced-excel.com/vlookup_function.html

But what if the Company is on the right side of Profits?

The usual workaround is to insert a column before A & copy the Company column over.  Then you can do the usual VLOOKUP.  The other way is to learn the seemingly more complex INDEX & MATCH.

Here’s a 3rd option by just learning a simple formula called CHOOSE.  Here’s what you need to input for the formula:

A simple scenario would be to return A1 or B1.  The index_num decides which value would be picked up.  So if my index_num is 1, it will pick up A1; because that’s the value1 I input.

And if I were to put in 2 as the index_num, it will pick up B1.

I can also pick up both of the numbers by adding the curly brackets here.  But picking up 2 numbers in a cell don’t make sense to Excel, so let’s add a SUM to it.  It will therefore pick up A1 AND B1 to sum them up.

You can even choose a range for value1 & value2 like this.  This is picking the range of A1:A2 & then B1:B2.

What if we reverse the range?  You’ll see no difference in the final value here, but internally it actually picks up B1:B2 and then A1:A2.

So if we go back to our VLOOKUP case & put in the column for us to look up as value1, and the result column as value2, we are actually forcing whatever column that is on the right to the left.

Pretty brilliant eh?

2 Responses to “Reverse VLOOKUP”

  1. Mansoor Sajid Says:

    Thanks for making VLOOKUP function more clearer.

  2. BenneTan BenneTan Says:

    glad to be able to help!

Leave a Reply

You must be logged in to post a comment.

s2Member®