How to Do a “Left Lookup” in MS Excel 2010 by Using MATCH() and INDEX() Functions

© Ugur Akinci

There are times when you would need to do a “left lookup” — that is, first go down a table COLUMN, find a value, and then find another ROW value to the LEFT of the cell in question.

Let’s express the same idea visually.

For example, imagine that in the table below we first want to find the SAT value “1325″ (Arrow 1). Then, we want to find the ID number of the student who had that SAT score (Arrow 2):

Obviously, an Excel function like VLOOKUP() will not be able to accomplish that kind of search since you can never lookup towards LEFT of your reference cell in VLOOKUP().

This is a perfect situation when you should use MATCH() and INDEX() functions together.

STEP 1: First, let’s find the SAT score we are interested in by using MATCH() function:

=MATCH(1325,D1:D9,0). The result is 7.

STEP 2: Let’s use the result above to find the value of the cell in COLUMN 1 (ID) of the same ROW (#7):

=INDEX(A1:F9,MATCH(1325,D1:D9,0),1). The result is 6.

This means that the ID number of the student who got 1325 in SAT is 6.