ogehelp.uga.edu
submit a ticket

Using INDEX, MATCH in Excel

Using the INDEX and MATCH formula

Setup Your Data

  1. To begin the process of comparing data, you need to have two sets of data to compare against. For simplicity, bring both data sets into a single Excel workbook.
  2. You need to decide what data (INDEX) do you want returned if a MATCH is found.
  3. Of the two data sets, I suggest adding the formula below on the sheet you will use for reporting. This may require some thinking. I'll call these ReportSheet and ReferenceSheet.
  4. Once you have choosen the primary data set, you need to insert a new column at the beginning/end (or where appropriate).
  5. Give this new column a Heading, for example, MATCH?. This column will be filtered in Excel.
  6. Now you're ready to craft the formula.

Writing the Formula

= INDEX( ReferenceSheet!column of data you want, MATCH( ReportSheet!ROW IDENTIFIER, ReferenceSheet!column of identifiers, 0 ))

Reporting Table (Preferred Color of Active Employees)
row number A (column of identifiers) B (color) C (item) D (MATCH?)
1 ID233 Green Eggs Active
2 ID235 Green Thumb Active
3 ID236 Blue Datsun Retired
4 ID236 Red Bull Retired
5 ID007 Black Aston Martin #N/A
Reference Table (Active Employees)
Row number A (unique identifier) B (HR status)
1 ID233 Active
2 ID235 Active
3 ID236 Retired

In this example, for an exact match, the final formula would be (in cell D1):

=INDEX( 'Reference Table'!B:B, MATCH( 'Reporting Table'!A1, 'Reference Table'A:A, 0 ))
Match type Behavior Details
1 Approximate MATCH finds the largest value less than or equal to lookup value. Lookup array must be sorted in ascending order.
0 Exact MATCH finds the first value exactly equal to lookup value. Lookup array does not need to be sorted.
-1 Approximate MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.
blank Approximate When match type is omitted, it defaults to 1 (approximate).

Additonal Resources