Exceljet

Quick, clean, and to the point

Left lookup with INDEX and MATCH

Excel formula: Left lookup with INDEX and MATCH
Generic formula 
=INDEX(range,MATCH(A1,id,0))
Summary 

To perform a left lookup with INDEX and MATCH, set up the MATCH function to locate the lookup value in the column that serves as an ID. Then use the INDEX function to retrieve values at that position. In the example shown, the formula in H5 is:

=INDEX(item,MATCH(G5,id,0))

where item (B5:B15) and id (E5:E15) are named ranges.

Explanation 

One of the advantages of using INDEX and MATCH over another lookup function like VLOOKUP is that INDEX and MATCH can easily work with lookup values in any column of the data.

In the example shown, columns B through E contain product data with a unique ID in column E. Using the ID as a lookup value, the table to the right uses INDEX and MATCH to retrieve the correct item, color, and price.

In each formula, the MATCH function is used to locate the position (row) of the product like this:

MATCH(G5,id,0) // returns 3

The lookup value comes from cell G5, the lookup array is the named range id (E5:E15), and match type is set to zero (0) for exact match. The result is 3, since, ID 1003 appears in the third row of the data. this value is returned directly to the INDEX function as the row number, and INDEX returns "T-shirt":

=INDEX(item,3) // returns "T-shirt"

The formulas in H5, I5, and J5 are as follows:

=INDEX(item,MATCH(G5,id,0)) // get item
=INDEX(color,MATCH(G5,id,0)) // get color
=INDEX(price,MATCH(G5,id,0)) // get price

Notice the MATCH function is used exactly the same way in each formula. The only difference in the formulas is the array given to INDEX. Once MATCH returns a result (3 for id 1003) we have:

=INDEX(item,3) // returns "T-shirt"
=INDEX(color,3) // returns "Black"
=INDEX(price,3) // returns 19

Without named ranges

The named ranges above are used for convenience only. The equivalent formulas without named ranges are:

=INDEX($B$5:$B$15,MATCH($G5,$E$5:$E$15,0)) // item
=INDEX($C$5:$C$15,MATCH($G5,$E$5:$E$15,0)) // color
=INDEX($D$5:$D$15,MATCH($G5,$E$5:$E$15,0)) // price

Ranges are now absolute references to allow copying without changing. The lookup value in $G5 is a mixed reference to lock the column only.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.