FormuladrillINDEX MATCH
INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) finds the position of a value in one column with MATCH, then INDEX returns the value at that position in another column. It works in any direction and does not break when columns move, which is why it is preferred over VLOOKUP in files without XLOOKUP.
Syntax
INDEX(return_range, MATCH(lookup_value, lookup_range, 0))| Part | What it is |
|---|---|
return_range | The column (or row) you want the answer from. |
lookup_value | The value to find. |
lookup_range | The column (or row) to search. Same length as return_range. |
0 | Exact match. MATCH defaults to 1 (approximate), so always type the 0. |
Examples
=INDEX(A2:A50, MATCH("P-102", C2:C50, 0))Result: The value in A on the row where C equals P-102
A left lookup: the lookup column C is to the right of the return column A.
=INDEX(C:C, MATCH(E2, A:A, 0))Result: Column C value for the first row where A equals E2
Whole columns are fine; the ranges just need to line up row for row.
=INDEX(B2:F10, MATCH("Medium", A2:A10, 0), MATCH("Blue", B1:F1, 0))Result: The cell where the Medium row meets the Blue column
Two-way lookup: one MATCH for the row, one for the column.
=IFERROR(INDEX(C:C, MATCH(E2, A:A, 0)), "Not found")Result: Not found when E2 is missing from column A
Common mistakes
- Wrong value returned: MATCH was written without the 0, so it did an approximate match on unsorted data. Add the third argument 0.
- #N/A: the lookup value is not in the lookup range, often because of a trailing space or a number stored as text.
- The result is off by a few rows: return_range starts on a different row than lookup_range (A1:A50 against C2:C50). Start both on the same row.
- #REF!: the MATCH position is larger than return_range, which happens when lookup_range is longer than return_range.
Practice INDEX MATCH
These drills check your answer on live data, free, no account needed.
- Look left with INDEX and MATCHThe employee name is in the last column, so VLOOKUP cannot reach the salary to its left. Use INDEX and MATCH to return the salary of the employee named in F1. Answer in F2.
- Price gridReturn the price for the size in G1 and the plan in G2 from the grid, in G3. Use INDEX with two MATCH functions.
Questions
Is INDEX MATCH better than VLOOKUP?
It is more flexible: it looks left, survives inserted columns and is faster on large sheets because it reads only two columns. VLOOKUP is shorter to type. In Excel 2021 or 365, XLOOKUP does the same job with less typing.
How do I do INDEX MATCH with two criteria?
Use =INDEX(C2:C50, MATCH(1, (A2:A50=E2)*(B2:B50=F2), 0)). In Excel 2021 or 365 it works as typed; in older versions confirm with Ctrl+Shift+Enter.
Why does MATCH need the 0?
Without it MATCH assumes the data is sorted ascending and returns the position of the largest value less than or equal to the lookup value, which gives silent wrong answers on unsorted lists.
Related
- INDEXHow the Excel INDEX function returns the cell at a given row and column of a range, with examples for a single column, a grid and the pairing with MATCH.
- MATCHHow the Excel MATCH function returns the position of a value in a row or column, with exact and approximate match types, examples and the causes of #N/A.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.