INDEX and MATCH in Excel: what they do and how to use them

MATCH finds which position a value sits at in a list, and INDEX returns whatever is at a given position. Put together, they do a lookup that can go in any direction.

Syntax

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

When to use it

Before XLOOKUP existed, this pair was the answer to everything VLOOKUP could not do. It can look to the left. It also keeps working when someone inserts a column in the middle of the table.

What trips people up

Remember the zero at the end of MATCH. Without it MATCH assumes the list is sorted and returns wrong answers without complaining.

Watch it built, step by step

Look up with INDEX and MATCH — Survives an inserted column

Questions

Why use INDEX and MATCH instead of VLOOKUP?

It can return a column to the left of the one it searches, it does not break when columns are inserted, and it works in every version of Excel.

What does the 0 in MATCH mean?

Exact match. Leave it out and MATCH assumes 1, an approximate match on sorted data, which quietly returns wrong answers on an unsorted list.

Can INDEX and MATCH look up on two conditions?

Yes: =INDEX(C2:C100,MATCH(1,(A2:A100=F1)*(B2:B100=F2),0)). In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Related functions

VLOOKUP — VLOOKUP searches down the first column of a range for a value, and returns something from a column further to the right on the same row.
XLOOKUP — XLOOKUP takes a value, looks for it in one column, and returns the matching item from another.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.