Lookup formula page
INDEX MATCH Formula
Use INDEX MATCH when you need an exact lookup that does not require the return column to sit to the right.
Create an INDEX MATCH formula for exact lookup.
If F2 is B-200, the formula returns 18.
Copy formulas
=INDEX(D2:D100, MATCH(F2, A2:A100, 0))=INDEX(D2:D100, MATCH(F2, A2:A100, 0))INDEX MATCH uses a separate return range and lookup range in both Excel and Google Sheets, with MATCH set to exact mode for these keys.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
If F2 is B-200, the formula returns 18.
How the formula works
- MATCH finds the row position of F2 in A2:A100.
- INDEX returns the value from D2:D100 at that position.
- The 0 in MATCH forces exact match.
| Syntax piece | Role in the formula |
|---|---|
| D2:D100 | The return range containing the value to bring back. |
| F2 | The lookup value to find. |
| A2:A100 | The lookup range containing the key values. |
| 0 | The MATCH mode that requires an exact key match. |
Verified examples
=INDEX(D2:D100, MATCH(F2, A2:A100, 0))Excel: Enter B-200 in F2 and use the sample product table with SKU in A and Price in D. Returns: 18
=INDEX(D2:D100, MATCH(F2, A2:A100, 0))Google Sheets: Enter A-101 in F2 and use the same sample table. Returns: 25
=IFERROR(INDEX(D2:D100,MATCH(F2,A2:A100,0)),"Not found")Excel: Enter C-999 in F2; that key is not in the sample table. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| INDEX MATCH returns an error despite a visible key | The lookup and return ranges do not cover the same number of rows. | Make D2:D100 and A2:A100 start and end on matching rows. |
| A matching key is not found | One key has hidden spaces or one side is text while the other is a number. | Normalize spaces and align the two cells' data types before matching. |
| The wrong row is returned | The lookup range contains duplicate keys. | Remove duplicates or accept that MATCH returns the first matching key. |
When not to use this formula
- Do not use INDEX MATCH when every matching row is needed; use FILTER instead.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Exact Match Formula | Use when XLOOKUP is available and separate lookup and return ranges are preferred. |
| INDEX MATCH Formula Builder | Use when the lookup and return ranges need to be configured interactively. |
Related formulas
Official references
- Look up values with VLOOKUP, INDEX, or MATCH from Microsoft
- INDEX function from Microsoft
FAQ
Why is MATCH set to 0?
0 requires an exact match, which is appropriate for SKUs, IDs, and other keys.
Can the INDEX return range be independent from the lookup range?
Yes. INDEX can return from D2:D100 while MATCH searches A2:A100, as long as the row positions align.