(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…
B11: =MATCH(A11,$A$3:$A$7,0)
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.
Excel's INDEX-MATCH lookup method in practice.
C11: =INDEX($B$3:$B$7,$B11)
This formula returns the item for the cell number specified by the value in cell B11.
D11: =INDEX($C$3:$C$7,$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:
D11: =VLOOKUP(A11,A3:B7,2,FALSE)
So far, INDEX-MATCH and VLOOKUP have the same capability. But now consider the formulas in cells B16 and C16:
B16: =MATCH(A16,$B$3:$B$7,0)
C16: =INDEX($A$3:$A$7,$B16)
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.
{ 2 comments }
I was in awe when first introduced to the INDEX-MATCH combination. I found it hard to implement because I could not remember what went where. Then I tripped over the Lookup Wizard add-in. The Wizard makes using INDEX-MATCH so much easier. Just a few points, clicks, and range selections, and you are done.
To turn on the add-in in Excel 2003, go to Tools > Add-Ins… > check Lookup Wizard. The Lookup… option is added near the bottom of the Tools menu.
To turn on the add-in in Excel 2007, go to Office > Excel Options > Add-Ins. Select the Lookup Wizard in the Inactive list of add-ins and select Go. In the Add-in pane, check Lookup Wizard. In the Formulas tab, a new group Solutions is added and includes an icon for Lookup.
Be aware when upgrading to Excel 2010. The Lookup Wizard as well as the Conditional Sum Wizard both go away and are replaced by the so-called Function Wizard, which is nothing more then the old insert function dialog which has been around forever. Considering all the useless bloat and legacy features still in Excel from years ago I find it ridiculous they chose to remove two of the more useful features. What did they gain from doing so?