INDEX MATCH
=INDEX(C2:C500, MATCH(F2, A2:A500, 0)) looks up F2 in column A and returns the value on the matching row of column C. MATCH does the searching and reports a position number; INDEX knows nothing about the search and simply hands back the nth item of whatever range it was given. Because the search range and the return range are named separately, the answer can sit anywhere — left of the key, on another sheet, in a different column order — and inserting or deleting columns between them breaks nothing, which is precisely the failure mode of VLOOKUP's hardcoded column number. The 0 in MATCH is not optional: without it MATCH assumes the list is sorted ascending and returns the nearest lower position, no error attached.
The formula
=INDEX($C$2:$C$500, MATCH($F$2, $A$2:$A$500, 0)) one-column lookup, in any direction
=INDEX($B$2:$E$500, MATCH($H$1, $A$2:$A$500, 0), MATCH($H$2, $B$1:$E$1, 0)) two-way: the row found by label, the column by header
=INDEX($C$2:$C$500, MATCH($F$2&$G$2, $A$2:$A$500&$B$2:$B$500, 0)) two criteria joined into one key (Ctrl+Shift+Enter before Excel 365)
=INDEX($C$2:$C$500, MATCH(1, ($A$2:$A$500=$F$2)*($B$2:$B$500=$G$2), 0)) the same idea by multiplication instead of joining
=IFERROR(INDEX($C$2:$C$500, MATCH($F$2, $A$2:$A$500, 0)), "not found") replaces the #N/A with something readableA worked example
A2:A500 holds SKUs, B2:B500 the warehouse each row refers to, C2:C500 the quantity on hand. F2 holds SKU-1042 and G2 holds West.
=INDEX($C$2:$C$500, MATCH($F$2&$G$2, $A$2:$A$500&$B$2:$B$500, 0))
86 — the quantity on the one row where both the SKU and the warehouse match. On Excel 2019 and earlier the same formula must be committed with Ctrl+Shift+Enter or it returns #VALUE!. And if the two ranges do not start on the same row — INDEX over C2:C500 with MATCH over A1:A500, say — every answer comes back shifted by one row, plausible and wrong.
Which one do I need?
| If you want to… | Use |
|---|---|
| The answer sits left of the key column | =INDEX(return_range, MATCH(key, key_range, 0)) — the one thing VLOOKUP cannot do at all |
| Both the row and the column have to be found (a matrix) | Two MATCHes inside one INDEX: one for the row label, one for the column header |
| Two or more values together identify the row | Join the keys with & on both sides of MATCH, or multiply the comparisons and match on 1 |
| Everyone opening the file has Excel 365 or 2021 | XLOOKUP is shorter and needs no exact-match argument |
| You only need the position, not the value | MATCH on its own already answers that |
| The formula returns #N/A | MATCH is not finding the value: confirm the 0 is present, then check for trailing spaces or numbers stored as text |
| The formula returns a value, but the wrong one | The INDEX range and the MATCH range do not begin on the same row — line their first rows up exactly |
Frequently asked questions
Why use INDEX MATCH instead of VLOOKUP?
Three reasons that all bite on real workbooks: the returned column can be to the left of the key, there is no hardcoded column count to break when someone inserts a column, and Excel only scans the two narrow ranges you named rather than the whole table, which is noticeable across tens of thousands of rows.
What does the 0 in MATCH do?
It demands an exact match and works on data in any order. Omitting it, or passing 1, asks for the largest value less than or equal to the lookup and assumes an ascending sort; -1 asks for the smallest value greater than or equal on a descending list. On unsorted data an omitted 0 produces a confident wrong answer rather than #N/A.
Do I still need Ctrl+Shift+Enter for INDEX MATCH?
Not on Excel 365 or 2021, where array behaviour is native. On Excel 2019 and earlier the plain one-column form is fine as a normal formula, but the multi-criteria versions — joining ranges with & or multiplying comparisons — must be committed with Ctrl+Shift+Enter or they return #VALUE!.
Why does INDEX MATCH return the wrong row?
Almost always misaligned ranges: INDEX over C2:C500 while MATCH searches A1:A500 offsets every result by one row, and nothing errors because both ranges are valid. Make the first row of the INDEX range and the first row of the MATCH range identical, or use whole columns for both so they cannot drift apart.
New guides and tools, once a month
DE + EN · double opt-in · no spam