INDEX MATCH in Excel
MATCH finds where a value sits in a list; INDEX returns whatever sits at a position in a range. Nest the first inside the second and you get a lookup that no longer cares which way the data runs — the answer VLOOKUP cannot give you when the key is on the right.
The INDEX MATCH pattern
Two functions, one lookup. MATCH reports a position; INDEX returns whatever is at that position. Neither half is a lookup on its own.
The column (or row) your answer lives in, e.g. C2:C5. It does not have to contain the key, and it may sit on either side of it.
The value to find, the range to search (the key column), and the match type. The match type must be 0 (exact); omit it and Excel uses 1 (approximate), which returns the wrong row as soon as the data is not sorted.
A worked lookup
Return the price for a SKU from a table whose key column is on the left.
| SKU | Product | Price |
|---|---|---|
| SKU-101 | Keyboard | 49.00 |
| SKU-102 | Mouse | 19.50 |
| SKU-103 | Monitor | 24.99 |
| SKU-104 | Dock | 89.00 |
MATCH returns 3 because SKU-103 is the third row of A2:A5; INDEX then returns the third cell of C2:C5, 24.99. Because the return range is named directly, it does not matter which side of the key the price sits on.
Row and column at once (two-dimensional lookup)
When the answer is defined by a row label and a column label, use two MATCH calls: the first finds the row, the second finds the column.
| Rep | Q1 | Q2 | Q3 |
|---|---|---|---|
| Ada | 120 | 150 | 180 |
| Ben | 90 | 110 | 140 |
| Cleo | 200 | 180 | 210 |
The row MATCH returns 2 (Ben) and the column MATCH returns 3 (Q3), so INDEX returns the cell at row 2 column 3 of B2:D4 — 140. Because rows and columns are found independently, reordering the table does not break the formula.
INDEX MATCH vs VLOOKUP
Writing the same lookup with VLOOKUP means telling it which column to return, counted from the key. That single difference decides whether the formula survives a new column.
| Aspect | VLOOKUP | INDEX MATCH |
|---|---|---|
| Lookup direction | Right of the key only | Either side |
| Inserting a column | Column number drifts, silently wrong | Names the range, unaffected |
| Return column | A number counted from the key | The range itself |
| Readability | Short | Longer, but the intent is explicit |
| Older Excel | Works | Works (XLOOKUP does not) |
On a wide table, or one that people keep adding columns to, INDEX MATCH is the safer choice. On a narrow table you will look at once, VLOOKUP's brevity wins. Where XLOOKUP exists, it is shortest of all: one formula covers the return value and the not-found case.
4 INDEX MATCH gotchas
1. Omitting MATCH's match type
MATCH(value, range) with no third argument means approximate match, and on unsorted data it returns an unrelated row. Always write the 0.
2. Mismatched ranges
Searching A2:A6 while returning from C2:C5 shifts every answer by one row. Both ranges must cover the same rows and start on the same row; if you want to search the whole table, add an offset to INDEX's row argument.
3. Text versus number keys
A key stored as text "103" never equals the number 103. This is the classic symptom right after importing a CSV: coerce one side with VALUE or TEXT before matching.
4. Enter instead of Ctrl+Shift+Enter on the array form
MATCH(1, (A2:A6="North")*(B2:B6="Q3"), 0) builds an array. Legacy Excel needs Ctrl+Shift+Enter; a plain Enter returns #N/A or 1.
Excel vs Google Sheets
Microsoft Excel (365 / Desktop)
Excel's INDEX takes three arguments (range, row, column) and MATCH takes three (value, range, match type). Excel 365 accepts the array multi-criteria form with a plain Enter. Omitting INDEX's row or column returns the entire column or row.
Google Sheets
Google Sheets uses the same syntax and the same A2:A5 range notation, so a formula written in Excel pastes in almost unchanged.
INDEX MATCH exercises
Pull values with INDEX and MATCH from the left of the key, or through a partial match. The formula has to change per row — a hardcoded answer fails the next case.
FAQ
What is the difference between INDEX MATCH and VLOOKUP?
VLOOKUP can only return columns to the right of the key, and it stores a column number, so inserting a column silently breaks it. INDEX MATCH names the return range directly and looks the position up separately, so it works in either direction and survives inserted columns.
Is INDEX MATCH still worth learning when XLOOKUP exists?
Yes. It runs in Excel 2019 and older, in shared files where colleagues are on those versions, and in Google Sheets. Interviewers still ask for it, and a lot of finance templates are built on it.
How do I use INDEX MATCH with multiple criteria?
Either concatenate the keys (=INDEX(C2:C6, MATCH(A2&B2, D2:D6&E2:E6, 0))) or use the array form (=INDEX(C2:C6, MATCH(1, (A2:A6="North")*(B2:B6="Q3"), 0))). Legacy Excel needs Ctrl+Shift+Enter for the array form; Excel 365 and Sheets do not.