Lookup and reference

INDEX MATCH in Excel: How the Combination Works, With Examples

MATCH finds the position of a value; INDEX returns what is at that position. Together they look up anything, in any direction.

INDEX and MATCH are two ordinary functions that become a lookup when you nest one inside the other. MATCH takes a value and tells you where it is — its position in a range, as a number. INDEX takes a position and tells you what is there. Feed the first into the second and you have looked something up.

Understanding them separately is the whole trick. =MATCH("Safety goggles", A2:A5, 0) returns 3, because Safety goggles is the third item in that range. =INDEX(D2:D5, 3) returns 7.25, because that is the third item in the price range. Nest them — =INDEX(D2:D5, MATCH("Safety goggles", A2:A5, 0)) — and the 3 never has to be written down.

This pair was the professional's answer to VLOOKUP for two decades, because it searches and returns independently: the lookup column can be anywhere relative to the return column, and inserting columns cannot break it. XLOOKUP now does the same job in one function, but INDEX+MATCH still works in every version of Excel ever shipped, which is why it remains worth knowing.

Syntax

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Arguments

return_range
Required
INDEX's first argument: the range holding the value you want back.
lookup_value
Required
MATCH's first argument: the value whose position you need.
lookup_range
Required
MATCH's second argument: the single row or column to search. It must be the same height as return_range.
match_type
Optional
MATCH's third argument. 0 means exact and is what you want. 1 needs ascending data, -1 needs descending. Leaving it out defaults to 1 and silently returns wrong answers on unsorted data.

The example data

Headers in row 1, data in A2:D5.

ABCD
1ProductCategoryStockPrice
2Cordless drillPower tools2489.99
3Extension leadElectrical14012.5
4Safety gogglesSafety687.25
5Work glovesSafety2105.4

Worked examples

=MATCH("Safety goggles", A2:A5, 0)

Result: 3

MATCH on its own: Safety goggles is the third cell in A2:A5. Note it returns a position within the range, not a sheet row number.

=INDEX(D2:D5, 3)

Result: 7.25

INDEX on its own: the third cell of the price range.

=INDEX(D2:D5, MATCH("Safety goggles", A2:A5, 0))

Result: 7.25

The two combined. MATCH works out the position, INDEX fetches the value at it.

=INDEX(A2:A5, MATCH(210, C2:C5, 0))

Result: Work gloves

Searching the stock column and returning the product name to its left — the lookup VLOOKUP cannot perform.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: INDEX-MATCH Combination

Common errors and how to fix them

#N/A

Why it happens: MATCH could not find the value, so it passed #N/A up to INDEX. The usual causes are trailing spaces, mixed text and numbers, or a genuinely absent value.

How to fix it: Test the MATCH on its own in a spare cell first. If it returns #N/A there, the problem is the search, not the INDEX.

#REF!

Why it happens: MATCH returned a position larger than return_range — which happens when lookup_range and return_range are different heights.

How to fix it: Make both ranges span exactly the same rows. A2:A100 paired with D2:D50 will misfire the moment a match lands past row 50.

#VALUE!

Why it happens: match_type is text, or INDEX was given a non-numeric position.

How to fix it: Check the third argument of MATCH is 0, 1 or -1 and not quoted.

Wrong value returned, no error

Why it happens: match_type was omitted, so it defaulted to 1 and matched approximately against unsorted data.

How to fix it: Always pass 0 as MATCH's third argument. Same class of silent bug as VLOOKUP's missing FALSE.

Tips worth knowing

  • Build it inside out: get MATCH returning the right number in its own cell, then wrap it in INDEX. Debugging the whole nest at once is much harder.
  • For a two-way lookup, give INDEX a whole table and use MATCH twice — once for the row, once for the column: =INDEX(A2:D5, MATCH(...), MATCH(...)).
  • MATCH returns a position relative to the range you gave it, not a sheet row. =MATCH(x, A2:A5, 0) returning 1 means row 2 of the sheet.
  • MATCH accepts wildcards with match_type 0: "Safety*" matches the first entry beginning with Safety.

Frequently asked questions

Why use INDEX MATCH instead of VLOOKUP?

Three reasons. It can return a column to the left of the one it searches. Inserting or deleting columns inside the table does not break it, because you name the return range rather than counting columns to it. And on very wide tables it only reads two columns rather than the whole block.

Is INDEX MATCH obsolete now that XLOOKUP exists?

For new work in Microsoft 365 or Excel 2021+, XLOOKUP is shorter and easier to read. INDEX+MATCH remains the right answer for workbooks that must open in older versions, and you still need to recognise it in files other people wrote.

Can INDEX MATCH look up on two criteria at once?

Yes, most cleanly by matching a concatenation: MATCH(A2&B2, range1&range2, 0), entered normally in Microsoft 365 or with Ctrl+Shift+Enter in older versions. FILTER is easier if your Excel has it.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions