Left lookup with INDEX and MATCH

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.