INDEX MATCH

How INDEX and MATCH work

Think of these as a two-step team. MATCH walks through a column or row looking for your lookup value and returns the position number when it finds it (3rd item, 12th item, etc.). INDEX takes a column or row plus a position number and returns the value at that position.

Used together, MATCH calculates the position and feeds that number straight into INDEX. The result is a lookup that doesn't care which column the value lives in or which direction it has to look. There's no column-counting like VLOOKUP requires, and inserting columns into your table doesn't break the formula.

= INDEX(array, row_num, [col_num])

= MATCH(lookup_value, lookup_array, [match_type])

Arguments

INDEX alone: get a value at a specific row

Before pairing, see what each function does on its own. INDEX takes a column and a row number and returns the value at that position. The row number is relative to the array you give it, not the worksheet's row number.

=INDEX(B2:B11,5)

• •B2:B11 → the column you're indexing into
• •5 → the position you want (1-based)
• •Returns → the 5th value in B2:B11, which is whatever sits in B6
• •Static position → this is just to show INDEX in isolation. The real power kicks in when MATCH calculates the position.

MATCH alone: find the position of a value

MATCH is the partner. Give it a value to find and a list to search, and it returns the position of the match. That's it. The third argument controls how MATCH compares, and you almost always want 0 (exact match).

=MATCH("Monitor 27\"",A2:A11,0)

• •"Monitor 27\"" → what to find (the lookup value)
• •A2:A11 → where to look (the lookup array)
• •0 → match type: 0 means exact match. The default of 1 is dangerous on unsorted data.
• •Returns → the position number, not the value itself

Combined: the classic VLOOKUP replacement

Now the magic. Drop MATCH into INDEX's row_num argument, and you have a lookup that finds the position automatically. This is the formula every senior analyst writes by reflex instead of VLOOKUP.

=INDEX(B2:B11,MATCH("Webcam HD",A2:A11,0))

• •B2:B11 → the column to return from (Price)
• •MATCH("Webcam HD",A2:A11,0) → calculates the position by finding Webcam HD in the Product column
• •The combo → INDEX gets the position from MATCH and returns the price from the matching row
• •Add new columns → doesn't break this formula. VLOOKUP would need its col_index_num updated.

Look left: the feature VLOOKUP literally cannot do

VLOOKUP can only look right. Your lookup column has to be first, and it returns from a column to its right. If your names are in column B and IDs are in column A and you want to look up by name and return the ID, VLOOKUP can't help. INDEX MATCH doesn't care about direction.

=INDEX(A2:A11,MATCH("Sarah Chen",B2:B11,0))

• •A2:A11 → the column to RETURN (Employee ID, sitting to the LEFT of names)
• •MATCH("Sarah Chen",B2:B11,0) → find the position by searching the Name column
• •No constraint → the return column can be left, right, or in a different sheet entirely
• •VLOOKUP equivalent → doesn't exist. Best you can do is restructure the sheet.

Two-way INDEX MATCH: lookup by row AND column header

Use MATCH twice (once for the row, once for the column) and INDEX returns the value at the intersection. This is the formula for cross-tabbed tables: revenue by region by quarter, headcount by department by level, anything where you read across both dimensions.

=INDEX(B2:E5,MATCH("West",A2:A5,0),MATCH("Q3",B1:E1,0))

• •B2:E5 → the data block, no headers (just the numbers)
• •MATCH("West",A2:A5,0) → find which row holds West (returns 4)
• •MATCH("Q3",B1:E1,0) → find which column holds Q3 (returns 3)
• •Result → row 4, column 3 of B2:E5: the West/Q3 cell

INDEX MATCH vs VLOOKUP

Both functions solve the same problem: find a value in a table and return something based on it. INDEX MATCH wins on flexibility, VLOOKUP wins on readability. Here's the honest comparison.

Where people go wrong

1. 01
   Forgetting the 0 in MATCH

   Leaving off the third argument (or passing 1) makes MATCH do approximate matching, which silently returns wrong positions on unsorted data. The formula returns a value but it's the value at a different row.

   Fix: Always pass 0 as MATCH's third argument unless you have a specific reason to use approximate matching.
2. 02
   Mismatched array sizes between INDEX and MATCH

   If MATCH searches B2:B100 (99 rows) and INDEX returns from A2:A50 (49 rows), Excel returns wrong values for any match in rows 51-100, or #REF! errors.

   Fix: Make sure the rows in INDEX's array line up with the rows in MATCH's lookup_array. Use the same row range for both.
3. 03
   Missing $ signs when copying down

   Drag a working INDEX MATCH formula down ten rows and the lookup arrays slide along with it. Result: formulas in lower rows search shifted ranges and return nonsense.

   Fix: Lock the array references with $ signs: =INDEX($A$2:$A$11,MATCH(B2,$B$2:$B$11,0)). The lookup value (B2) stays relative so each row searches for its own value.
4. 04
   Using the wrong INDEX overload for two-way lookups

   INDEX has two forms. The single-array form takes one position. The full form takes both row_num and col_num. If you forget the col_num for a two-way lookup, INDEX returns the entire row instead of one cell.

   Fix: For two-way lookups, always pass both INDEX(array, MATCH(...), MATCH(...)). The third argument is what makes it cross-tab.

Notes

• INDEX is one of the few Excel functions that returns a reference rather than just a value, which means you can use it inside dynamic ranges
• MATCH returns a 1-based position. Position 1 is the first item of the lookup_array, not the first row of the worksheet
• match_type 0 is exact match. 1 is approximate (data must be sorted ascending). -1 is approximate the other way (data must be sorted descending)
• INDEX MATCH is consistently faster than VLOOKUP on large sheets because Excel only evaluates the columns you reference, not the whole table
• Both functions are case-insensitive when matching text: "Apple" equals "APPLE"
• Wildcards (* and ?) work in MATCH when match_type is 0
• When MATCH can't find the value it returns #N/A, which then propagates through INDEX. Wrap the whole thing in IFERROR to handle this gracefully
• XLOOKUP combines INDEX and MATCH into one function in Excel 365 and 2021+. Same power, simpler syntax.

What to learn next

Now prove it