# Left lookup with INDEX and MATCH

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:

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

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:

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:

### Without named ranges

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

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

## 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.