Excel · Lookup
Find Any Value Instantly with INDEX+MATCH (More Flexible Than VLOOKUP)
For accurate lookups that keep working even when column order changes, use INDEX+MATCH! Learn single- and multiple-criteria lookups as well as two-way lookups quickly with practical examples.
Why INDEX+MATCH?
- Flexibility: No left-to-right restriction (look up in any direction)
- Reliability: Formulas do not break when columns are added or moved
- Accuracy: Cleanly supports multiple-criteria and two-way lookups
Sample Data
| Product | Region | Price | Inventory |
|---|---|---|---|
| Keyboard | Seoul | 45000 | 23 |
| Mouse | Busan | 18000 | 41 |
| Monitor | Seoul | 220000 | 12 |
| Keyboard | Busan | 47000 | 15 |
Basics – Single-Criteria Lookup
Goal: Find the price in the row where the product is Mouse
=INDEX(C2:C5, MATCH("Mouse", A2:A5, 0))- INDEX(return_range, row_number) · MATCH(lookup_value, range, 0=exact match)
- Use
0for exact matches (to prevent approximate-match errors).
Two-Way (Row + Column) Lookup
Goal: Find the Inventory value for Keyboard
=INDEX(A2:D5, MATCH("Keyboard", A2:A5, 0), MATCH("Inventory", A1:D1, 0))Find the row by product name and the column by header name, then use INDEX to return the intersecting value.
Multiple-Criteria Lookup
Goal: Find the Price that meets both the Keyboard and Busan criteria
=INDEX(C2:C5, MATCH(1, (A2:A5="Keyboard")*(B2:B5="Busan"), 0))Array formula: Some older versions require Ctrl+Shift+Enter. Microsoft 365 uses dynamic arrays automatically.
- Multiplying multiple criteria arrays produces
1only in rows where all conditions are true. - For errors, use
IFERROR(formula, "Not found")to provide a user-friendly message.
Comparison Table – INDEX+MATCH vs. VLOOKUP vs. XLOOKUP
| Feature | VLOOKUP | INDEX+MATCH | XLOOKUP |
|---|---|---|---|
| Lookup direction | Left to right only | No restrictions | No restrictions |
| Adding/moving columns | Vulnerable | Reliable | Reliable |
| Multiple criteria | Helper column required | Directly possible with arrays | Recommended with FILTER |
| Syntax simplicity | Moderate | Intermediate | Very simple (newer versions only) |
Practical Tips & Error Handling
- Handle results cleanly with IFERROR:
=IFERROR(formula, "Not found") - Standardize data types: Mismatched number and date formats are a leading cause of MATCH failures
- Dynamic ranges: Build dynamic ranges using a Table name or
INDEXto reduce maintenance - Performance: For thousands of rows or more, specify precise ranges instead of unnecessary full-column references
Summary
| Key Point | Practical Checklist |
|---|---|
| INDEX+MATCH = flexible, accurate lookups | Supports both two-way and multiple-criteria lookups |
| Use exact matching | The third MATCH argument is 0 |
| Reliable reports | A structure that does not break when columns move |
FAQ
When should I use INDEX+MATCH instead of VLOOKUP?
INDEX+MATCH is more reliable when the lookup column is not on the left (a reverse lookup) or when columns are frequently added or moved in reports.
What if I have three or more criteria?
Expand the formula by multiplying additional arrays, such as (criteria1)*(criteria2)*(criteria3).
Why am I getting #N/A?
It is usually caused by mismatched values (spaces, formatting, or letter case) or an approximate match (not using 0). Use TRIM(), standardize formats, and handle errors with IFERROR.