Why you need this
Before XLOOKUP, INDEX MATCH was the pro's choice. It allows you to look up values to the left (which VLOOKUP can't do) and is faster on large datasets.
One-Click Copy
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Copy
Quick context
- • Works in both Excel and Google Sheets.
- • Use when you need consistent, auditable results.
- • Copy the snippet above and adjust only the ranges.
When to use
Learn how to use the Excel INDEX MATCH Formula formula.
Disclaimer: While we strive for accuracy, these formulas are provided "as is" without warranty of any kind. Please verify all results before use.
Common Mistakes
- ✕ Mismatched array sizes. Ensure the INDEX array and the MATCH lookup_array cover the exact same vertical or horizontal span.
- ✕ Forgetting 0 (Exact Match). MATCH defaults to approximate match (1), which requires sorted data and often returns wrong results for unsorted lookup lists.
Best Practices
- ✓ Prefer INDEX MATCH over VLOOKUP. It is faster on large datasets because it doesn't load the entire table, only the specific columns needed.
- ✓ Switch to XLOOKUP. XLOOKUP performs the same job as INDEX MATCH but with simpler syntax and built-in error handling.
Scalability Warning
Complex nested formulas are hard to audit. If you leave, no one else will understand this sheet.
See data Alternatives