INDEX MATCH in Excel: Flexible Lookup Examples
By Szabó Gergő · Updated
INDEX returns a value at a position, while MATCH finds that position. Together they create a durable lookup that can search in any direction and works in Excel versions without XLOOKUP.
Syntax and arguments
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))- return_range
- The row or column containing the result.
- lookup_value
- The value you want to find.
- lookup_range
- The row or column MATCH searches.
- match_type
- Use 0 for an exact match in standard business lookups.
INDEX MATCH examples
SKUs are in A2:A100, prices are in D2:D100, and F2 contains the SKU.
=INDEX(D2:D100,MATCH(F2,A2:A100,0))MATCH finds the SKU position and INDEX returns the value at that position in the price range.
Employee IDs are in D2:D100 and names are in B2:B100.
=INDEX(B2:B100,MATCH(G2,D2:D100,0))The return range can be anywhere, including left of the lookup range.
Products are in A2:A20, months are in B1:M1, and values fill B2:M20.
=INDEX(B2:M20,MATCH(P2,A2:A20,0),MATCH(P3,B1:M1,0))One MATCH chooses the product row and the other chooses the month column.
Common mistakes
MATCH defaults to approximate behavior
Include 0 as the third MATCH argument when you need an exact match.
Ranges start on different rows
Align lookup_range and return_range so the same relative position represents the same record.
Formula is difficult to audit
Use Formula Evaluation or split MATCH into a helper cell while troubleshooting.
INDEX MATCH FAQ
Is INDEX MATCH still useful when XLOOKUP exists?
Yes. It supports older Excel versions, works well in established models, and can be efficient for complex two-way lookups.
Why does MATCH return #N/A?
No exact value was found. Check hidden spaces, text-versus-number mismatches, and whether match_type is 0.
Can INDEX MATCH use multiple criteria?
Yes, but the formula is more advanced. Modern Excel can MATCH 1 against multiplied Boolean condition arrays.