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: