Common Errors & Fixes
INDEX MATCH returns #N/A or wrong value
Causes:- Lookup range and return range have different heights (rows).
- MATCH type wrong: use 0 for exact match; -1/1 for sorted lookup.
- Data type mismatch between lookup value and lookup range.
Fixes:- Use same-sized single-column ranges for lookup_range and return_range.
- Use 0 for exact match in most cases.
- Normalize types with TRIM, VALUE, or TEXT.
Frequently Asked Questions
What does INDEX MATCH do?
INDEX returns a value from a range by position; MATCH finds the position of a value. Combined, they look up a value and return from another column—and can look to the left, unlike VLOOKUP.
Why use INDEX MATCH instead of VLOOKUP?
INDEX MATCH can look left, is not broken when you insert columns, and often performs better on large data. It is more flexible than VLOOKUP.
How do I use INDEX MATCH with multiple criteria?
Use MATCH with an array formula or helper column that concatenates criteria. In Excel 365 you can use XLOOKUP with multiple conditions more easily.
What is the MATCH type (0, -1, 1)?
0 = exact match. -1 = find smallest value >= lookup (ascending). 1 = find largest value <= lookup (descending). Use 0 for most lookups.
Why is INDEX MATCH returning #N/A?
MATCH returns #N/A when the lookup value is not found. Check for data type mismatch (number vs text), extra spaces, or use IFERROR to handle not found.
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More