Excel’s Best Lookup Method: INDEX-MATCH

by Charley Kyd on December 15, 2010

(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…

[click to continue…]


General Ledger December 15, 2010 at 1:39 pm

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.

mmyett December 16, 2010 at 7:26 am

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?

Previous post:

Next post: