(Part 5 of 5. See Part 4.)
The most powerful and flexible way to look up data in Excel is the INDEX-MATCH method. It relies on two Excel functions:
=INDEX(reference, row_num, column_num)
- reference—a range of cells
- row_num—the row in reference from which to return data.
- column_num—the column in reference from which to return data.
If reference is one row or column, INDEX can use this syntax: =INDEX(reference, cell_num)
=MATCH(lookup_value, lookup_array, match_type)
- lookup_value—The value to match in lookup_array.
- lookup_array—A range of cells with data.
- match_type—Specifies how Excel matches the lookup_value with values in the lookup_array. For exact matches, always use 0 for this argument.
The figure below shows the INDEX-MATCH method in action…
This formula tells us that the text “G23” is found in the third row of the SKUs column. And the formula copied to B12 says that “A101” is found in the second row.
Notice that because we want an exact match, the last argument in cell B11 has a value of zero.
This formula returns the item for the cell number specified by the value in cell B11.
And this formula returns the price for the cell number specified in cell B11.
Of course, we could have combined both the INDEX and MATCH in one formula. That is, cell D11 could have been:
D11: =INDEX($C$3:$C$7, MATCH(A11,$A$3:$A$7,0))
This combination works just like a VLOOKUP function:
So far, INDEX-MATCH and VLOOKUP have the same capability. But now consider the formulas in cells B16 and C16:
Here, we look up “Ties” in the column of Items and return its SKU from the column of SKUs. This is a result that VLOOKUP can’t produce, as I explained in The Limitations of VLOOKUP and HLOOKUP.
The INDEX-MATCH method has many additional advantages that you’ll discover as you use this method.
If you aren’t using INDEX-MATCH for your lookups, you’re missing a lot of power. Give it a try.