Excel + Google Sheets · Deep guide
INDEX and MATCH in Excel: a flexible lookup
INDEX and MATCH are two small functions that, used together, form one of the most reliable lookups in Excel. MATCH finds the position of a key in a list, and INDEX returns the value at that position from another column. Because they work with separate columns, they keep working when you insert or move columns.
The pair works in Excel 2010 or later, in Excel 365 and in Google Sheets, so it is the safest choice for files shared with people on older versions. In this guide you will see the syntax, a worked example with real numbers and the usual mistakes. See also: VLOOKUP guide and COUNTIF guide.
How INDEX + MATCH works
MATCH takes the key, the list and a match type, and INDEX takes the column to return and the position. Write them together like this:
=INDEX(C2:C6,MATCH("c",B2:B6,0))MATCH looks for the letter c in column B and gives its position. INDEX uses that position to return the value from column C. The final 0 asks for an exact match, and unlike VLOOKUP the key column can be anywhere. Works in Excel 2010 or later.
Step by step example
- Type the keys in column B and the values in column C.
- Click an empty cell, for example E2.
- Type
=INDEX(C2:C6,MATCH("c",B2:B6,0))and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Category (B) | Price (C) |
| 2 | a | 10 |
| 3 | b | 20 |
| 4 | c | 30 |
| 5 | b | 40 |
| 6 | d | 50 |
The expected result is 30, the value that sits next to c. If the key appears twice, MATCH returns the first position, so check for duplicates before you trust the result.
Common errors
- You forget the final 0 and MATCH uses approximate matching, which gives wrong values on unsorted data.
- The key does not exist and Excel shows the not available error. Wrap the formula in IFERROR to show a friendly message.
- The two ranges have different sizes, so the position points to the wrong row. Both must start and end on the same rows.
- Numbers stored as text do not match real numbers. Convert both sides to the same type.
If you use Excel 365 you can also choose XLOOKUP, which does the same in one function. INDEX and MATCH remain the best choice when a file must open correctly in older Excel versions.
The same formula in other languages
- English INDEX and MATCH
- Español INDICE y COINCIDIR
- Português ÍNDICE e CORRESP
- Deutsch INDEX und VERGLEICH