Tom’s Tutorials for Excel: Reverse Lookup

Tom’s Tutorials for Excel: Reverse Lookup

As you probably know, the VLOOKUP function searches the first column of a range of cells and returns a value from a cell in the same row. But what if your criteria item — that is, what you are trying to look up — is not listed in the first column of the range? What if you need to return a value from a column to the left of where your criteria is listed in the table?

In the picture, a department store keeps a daily table of transactions, with typical column fields relating to their sales. To the right of the table are colored cells with examples for doing a reverse lookup using the INDEX and MATCH functions.

In cell H2, the criteria for Order ID is manually entered to return its associated Product ID in the green cell I2. Notice in the table that Order ID is located in column F and Product ID is located in column B, so an alternative to VLOOKUP is needed.

In this example, the formula in cell I2 is

`=INDEX(A1:F100,MATCH(H2,F1:F100,0),2)`

You can interpret this formula as:
=INDEX(table-range, MATCH(criteria, criteria_column, match_exact), return_column_2)
Note that column 2 is column B, Product ID.

The second example in yellow cell I7 demonstrates the same principle. The formula there to find the Sales associated with the criteria for Manager is

`=INDEX(A1:F100,MATCH(H7,E1:E100,0),4)`

In this example, it is the same table range of A1:F100, “Tony” as the Manager criteria is located in column E, and the Sale associated with Tony would be in column D which is column #4 in the table.

Using INDEX and MATCH is a powerful combination for lookup routines, and it can be used for looking up values to the right of the criteria column just as VLOOKUP does. Personally, I almost never use VLOOKUP because I find INDEX and MATCH to be more versatile, not requiring the table to be sorted in any way, nor having its column fields arranged in a certain order. Every Excel user has their own preferences. If you are a dedicated VLOOKUP user, perhaps consider the INDEX and MATCH approach, which you may find to be a valuable alternative.

8 comments on “Tom’s Tutorials for Excel: Reverse Lookup”
1. bushra says:

I need to Learn financial Modeling tools in Excel

2. Hi Tom,
Nicely done, never though of it.
I added your solution to one of my example files on my site.

3. indzara says:

I used to use VLOOKUP at the beginning. Once I knew about INDEX and MATCH, I don’t use VLOOKUP at all. INDEX/MATCH provide so much flexibility and it’s the function combo that I use the most everyday. 🙂

• DIPSUNDAR DUTTA says:

REQUEST TO EVERYONE, HOW VLOOKUP / INDEX & MATCH USE WHEN MULTIPLE DATE REQUIRED IN A PARTICULAR ROW ON A PARTICULAR VALUE?

4. Mithun Kumar Reddy says:

Using Vlookup with CHOOSE

=Vlookup(Tony,choose({1,2},F1:F100,D1:D100),2,0)

• Tom Urtis says:

I blogged here about using CHOOSE for reverse VLOOKUPs, so there’s no need to repeat every alternative for similar-topic postings.

5. usman says:

Please share data file for practice..

• Tom Urtis says:

I create these examples that are relatively uncomplicated for my visitors to re-create for themselves. My objective is to increase the user’s familiarity with the scenario and why the (in this case) formula works. I do include downloadable workbooks for more complex examples.