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.