Skip to content
Courses

INDEX MATCH

INDEX MATCH is two Excel functions used together to look up a value: MATCH finds where your value is, and INDEX brings back the data from another column at that spot.

Select any words here to ask your Maestro about them.

What does =MATCH("SKU-140", A2:A9, 0) return?

An office supply store's price list, unsorted. Example data.

How it works: MATCH finds the value you're looking for in one column, and INDEX brings back what's at the same height in another. Together: =INDEX(C2:C9, MATCH(F1, A2:A9, 0)).

Why people use it: it works in every Excel version and in Google Sheets, it can return a column to the left of the one you search, and it doesn't break when you insert columns, unlike VLOOKUP. In Excel 365 and Excel 2021 or later, XLOOKUP does the same job in one function.

Where it breaks: leave out the 0 and MATCH assumes the column is sorted, so on unsorted data it can return the wrong row without an error.

How it's used

The full formula

INDEX MATCH, step by step

  1. Type the SKU you want in F1
  2. =MATCH(F1, A2:A9, 0)Finds that SKU in A2:A9. The 0 asks for an exact match.
  3. marked: =INDEX(C2:C9, MATCH(F1, A2:A9, 0))Brings back the price at the same height in C2:C9.
  4. Swap C2:C9 for B2:B9 to get the product name instead
If MATCH can't find the SKU, you get #N/A. Wrap the formula in IFERROR only once you know why it's missing.

When to look closer

A code you can see but MATCH can't find

Orders imported from a CSV

  1. A7 shows 10482, and you typed 10482 in F1
  2. marked: =MATCH(F1, A2:A9, 0) returns #N/A

You can see order 10482 in A7, but MATCH can't find it. Why?

Ask your Maestro about INDEX MATCH

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "INDEX MATCH" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which value do you look up by hand in another sheet every week?

Asking about: Answers from the page "How do I count the missing values in each column with pandas?"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • XLOOKUP

    XLOOKUP looks for a value in one column and returns the data from the same row in another column, to the right or the left: =XLOOKUP(E2,B2:B9,C2:C9).

  • VLOOKUP

    VLOOKUP looks for a value in a table's first column and returns the data from another column in that row: =VLOOKUP(E2,A2:C9,3,FALSE).

  • Pivot table

    A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.

  • CSV

    A CSV (comma-separated values) is a plain-text file that holds a table: one row per line, with commas between the columns.

The whole glossary