How to Use INDEX MATCH in Excel: A Practical Guide
Master INDEX MATCH in Excel with practical examples: learn single- and multi-criteria lookups, 2D table reads, robust error handling, and tips to optimize performance.
INDEX and MATCH form a powerful, flexible lookup duo in Excel. MATCH locates the position of a value in a row or column, then INDEX returns the value at that position. This combination is more versatile than VLOOKUP, especially for left lookups and ranges, and supports multiple criteria with formulas.
Understanding INDEX and MATCH
INDEX and MATCH work together to perform lookups without being constrained by column order. MATCH returns the position of a lookup value within a range, while INDEX uses that position to fetch the value from the desired row and column. This pairing is notably more flexible than VLOOKUP because it does not require the lookup column to be the leftmost column, and it scales well with large datasets.
=INDEX(B2:B100, MATCH("Widget", A2:A100, 0))- This example looks up 'Widget' in column A and returns the corresponding value from column B.
=INDEX(Products!C:C, MATCH("Widget", Products!A:A, 0))- If your data is on another sheet, you can reference that sheet's ranges directly.
description-longer-or-other-context-description-firewalld-omitted?":""},{"text":""}
code_examples_in_block=true
note__only__to__show__format
end
Steps
Estimated time: 30-45 minutes
- 1
Plan data layout
Identify the lookup column and the return column. Decide if you need a 2D lookup (row and column) or a simple single-criteria lookup. Prepare named ranges for readability if possible.
Tip: Label ranges clearly and consider using absolute references (A$2:A$100) to keep ranges fixed when copying. - 2
Create a basic lookup
Write a simple INDEX/MATCH to fetch a value by a single key. Start with a small sample table to validate the result.
Tip: Test with a known key first to verify behavior. - 3
Extend to 2D lookups
If you need to read from a grid, add a second MATCH to select the correct column header. Verify headers match exactly.
Tip: Ensure headers and row labels are consistent. - 4
Add multi-criteria lookups
Use a compound condition to filter by several columns. This often requires array syntax in older Excel versions.
Tip: In older Excel, remember to enter as an array formula (Ctrl+Shift+Enter). - 5
Handle missing data
Wrap the formula with IFERROR to return a friendly message when no match is found.
Tip: Choose a clear fallback like "Not found" or blank. - 6
Validate and optimize
Cross-check against a known dataset and limit ranges to improve performance on large sheets.
Tip: Prefer named ranges for readability and maintenance.
Prerequisites
Required
- Required
- Required
- Basic knowledge of Excel formulasRequired
- A dataset with at least two columns (lookup and result)Required
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy results or formulas | Ctrl+C |
| PastePaste results or formulas | Ctrl+V |
| Fill downExtend formulas down a column | Ctrl+D |
| Edit active cellEdit formula in place | F2 |
People Also Ask
What is INDEX MATCH used for?
INDEX MATCH retrieves a value from a table by locating its row and column using MATCH, then returning the intersecting value with INDEX. It supports left lookups and works with dynamic ranges.
INDEX MATCH helps you pull a value by locating where it is in the table and returning the corresponding result, even if the lookup column isn’t on the far left.
Can INDEX MATCH replace VLOOKUP in all cases?
In most scenarios, INDEX MATCH can substitute VLOOKUP and offers more flexibility, especially for left lookups and large datasets. For simple tasks, VLOOKUP remains convenient.
Yes, INDEX MATCH often replaces VLOOKUP with more flexibility, though VLOOKUP is quicker for basic checks.
Do I need to press Ctrl+Shift+Enter for multi-criteria lookups?
Older Excel versions required Ctrl+Shift+Enter for multi-criteria INDEX MATCH. Modern Excel supports dynamic arrays, so you can often enter it normally.
Some old sheets need Ctrl+Shift+Enter; newer Excel handles the multi-criteria form more smoothly.
When should I use XLOOKUP over INDEX MATCH?
XLOOKUP is a newer, single-function alternative with simpler syntax. INDEX MATCH remains valuable for compatibility with older Excel and for left-lookups.
XLOOKUP is simpler in new Excel, but INDEX MATCH still shines on older versions and for left-side lookups.
The Essentials
- INDEX MATCH is more flexible than VLOOKUP.
- Use exact match (0) for reliability.
- 2D lookups require separate row and column MATCHes.
- For multi-criteria, combine boolean expressions in MATCH.
- IFERROR helps return friendly results when no match is found.