INDEX and MATCH
INDEX and MATCH are two simple functions that, combined, form the most flexible lookup technique in classic Excel — powerful enough that many experienced analysts still prefer it over XLOOKUP for certain jobs. This lesson breaks each function down individually, then shows exactly how and why they combine, including two-way lookups and dynamic column selection that VLOOKUP simply can't do.
Why this skill matters professionally
INDEX/MATCH appears constantly in legacy financial models, banking templates, and workbooks built by analysts trained before XLOOKUP existed — which is still most of the corporate world. Recruiters in finance, operations, and data analysis roles often specifically ask about INDEX/MATCH because it demonstrates a deeper understanding of how lookups work internally, not just a memorized formula. Being fluent in it lets you read, audit, and confidently modify inherited models instead of being intimidated by them.
Learning objectives
- Combine INDEX and MATCH to look up in any direction.
Background concepts
Two separate ideas
INDEX and MATCH each do something simple on their own. INDEX returns the value at a given position in a range. MATCH returns the position of a value within a range. Neither one 'looks things up' on its own — combining them is what creates a lookup.
Why combine them at all
MATCH finds the row number of what you're searching for. INDEX then uses that row number to fetch a value from any column you choose — not restricted to being to the right of the search column like VLOOKUP.
The mental model
Think of MATCH as answering 'which row is this?' and INDEX as answering 'what's in this row?'. Chaining them together: MATCH finds the row, and that number gets fed straight into INDEX.
Why it predates and survives XLOOKUP
INDEX/MATCH could already do everything XLOOKUP does — search left, search right, two-way lookups — years before XLOOKUP existed. XLOOKUP is essentially a more convenient packaging of the same underlying logic, which is why many veteran Excel users see no urgent need to abandon INDEX/MATCH.
Step-by-step
1. Step 1: Understand INDEX alone
INDEX returns whatever is at a specific position in a range.
=INDEX(A2:A100, 5) -> returns the 5th value in A2:A100 =INDEX(A2:C100, 5, 2) -> returns the value in the 5th row, 2nd column of A2:C100
2. Step 2: Understand MATCH alone
MATCH returns a position number, not the value itself.
=MATCH("SKU-204", A2:A100, 0) -> e.g. returns 5, meaning SKU-204 is the 5th item
(0 = exact match, always use this)3. Step 3: Chain them together
Feed MATCH's result directly into INDEX's position argument.
=INDEX(C2:C100, MATCH("SKU-204", A2:A100, 0))
| |
| +-- finds the row position of SKU-204
+-- returns the value from column C at that row4. Step 4: Look up to the left
Unlike VLOOKUP, the return column can sit to the left of the search column with zero extra effort.
Table: Price(A) | Name(B) | SKU(C)
=INDEX(A2:A100, MATCH("SKU-204", C2:C100, 0)) -> returns Price, even though SKU is in column C5. Step 5: Two-way lookup (row and column both dynamic)
Use two MATCH functions inside one INDEX — one for the row, one for the column.
=INDEX(DataGrid, MATCH(RowKey, RowHeaders, 0), MATCH(ColKey, ColHeaders, 0))
Example: find the sales figure for "West" region in "Q3":
=INDEX(B2:E10, MATCH("West", A2:A10, 0), MATCH("Q3", B1:E1, 0))6. Step 6: Making it resilient to inserted columns
Because MATCH searches by header name rather than a hardcoded column number, inserting a new column into the middle of the table doesn't break the formula the way it would break VLOOKUP's col_index_num.
7. Step 7: Handling not-found results
Wrap the whole combination in IFERROR since neither INDEX nor MATCH has a built-in not-found argument like XLOOKUP.
=IFERROR(INDEX(C2:C100, MATCH(A2, A2:A100, 0)), "Not found")
8. Step 8: Speed tip for huge datasets
MATCH with exact match (0) doesn't require sorted data, but if your dataset is genuinely huge (100,000+ rows) and sorted, MATCH with 1 or -1 (approximate) runs faster using a binary search — same trade-off as XLOOKUP's search_mode.
Real-world workplace examples
Financial model with a header row of years
An FP&A analyst builds a model where columns are years (2021–2026) and uses INDEX/MATCH so that adding a new year column doesn't require rewriting every formula.
Looking up an ID from a name column that sits to the right of price
A retail buyer needs a product's cost, but the cost column is left of the product name column — impossible for a plain VLOOKUP without rearranging data, trivial for INDEX/MATCH.
Reverse lookup: last matching value
An operations lead uses INDEX/MATCH with array logic to find the most recent status update for an order ID in a long log, searching from the bottom up.
Dashboard grid lookup
An analyst builds a two-way INDEX/MATCH to pull a specific KPI value where rows are departments and columns are months, letting a dropdown selector drive both dimensions.
Practical scenarios
Auditing a banking model built by a departed analyst
A new analyst inherits a loan amortization model riddled with INDEX/MATCH formulas referencing named ranges like LoanTerms and RateTable. Rather than being intimidated, they use Ctrl+[ (Trace Precedents shortcut via Go To Special) and the Name Manager to map out what each named range covers, confirming MATCH's exact-match flag (0) is used consistently before trusting the model's outputs.
Explaining the tradeoff to a manager who wants XLOOKUP everywhere
A manager asks the team to convert all INDEX/MATCH formulas to XLOOKUP for 'modernization.' The analyst points out that INDEX/MATCH already handles every case the model needs, the conversion offers no functional benefit, and the effort would be better spent elsewhere — a good example of not changing working formulas just because a newer function exists.
Common mistakes beginners make
Forgetting the 0 in MATCH
Omitting the third argument makes MATCH assume approximate match on sorted data, which can silently return the wrong position on unsorted real-world data. Always type the 0 explicitly.
Mismatched range sizes between INDEX and MATCH
If INDEX's range is A2:A100 but MATCH searches C2:C150, the position MATCH returns won't correspond to the right row in INDEX's range. Both ranges must start and end on the same rows.
Using MATCH's result directly as a cell reference
MATCH returns a position number (like 5), not a cell address — trying to use it alone (e.g. in a chart reference) without wrapping it in INDEX won't work as expected.
Two-way lookup with mismatched header order
If the row-header MATCH and column-header MATCH ranges don't align exactly with INDEX's grid boundaries, the formula returns a value from the wrong cell entirely, often without an obvious error.
Not locking ranges before copying down
Like any lookup, forgetting $ signs on the ranges causes them to drift as the formula is copied, eventually referencing the wrong or blank data.
Best practices
- Always use exact match (0) in MATCH unless you deliberately want approximate/bracket matching.
- Keep INDEX's range and MATCH's range the same size and starting row.
- Use named ranges or Excel Tables so INDEX/MATCH formulas stay readable and stable.
- Prefer MATCH on header names over hardcoded column numbers for resilience.
- Wrap the combination in IFERROR to show a friendly message instead of #N/A.
- Use two nested MATCH calls for two-way lookups instead of building a helper column.
- Document two-way lookup formulas with a comment — they're powerful but not self-explanatory to the next reader.
Professional tips
- Select just the MATCH() portion of a formula in the formula bar and press F9 to see the row number it resolves to — great for debugging two-way lookups.
- INDEX can also return an entire row or column if you leave the row or column argument blank, e.g. =INDEX(A2:C100,5,) returns the whole 5th row.
- You can use INDEX/MATCH inside data validation lists to build dependent dropdowns.
- MATCH's wildcard support (with match_type 0) lets you do partial-text lookups, e.g. MATCH("*chen*", A2:A100, 0).
- Combine INDEX/MATCH with MAX or LARGE to find 'top N' lookups, like the highest sale and which rep made it.
Practice exercises
Build a left-lookup
Create a table where the value you want to return sits to the left of the search column, and prove INDEX/MATCH can retrieve it in one formula — something VLOOKUP alone cannot do.
Build a two-way lookup
Create a small grid of regions (rows) by quarters (columns) and write a single INDEX/MATCH/MATCH formula that returns the value at any chosen intersection.
Add resilience
Insert a new column into the middle of your source table and confirm your INDEX/MATCH formula (using header-name MATCH) still returns the correct value.
Wrap with IFERROR
Test your formula with a lookup value that doesn't exist and add IFERROR to show 'Not found' cleanly.
Review questions
What does INDEX alone return?
The value located at a specific row (and optionally column) position within a range.
What does MATCH alone return?
The numeric position of a value within a range, not the value itself.
Why can INDEX/MATCH look up to the left, unlike VLOOKUP?
Because INDEX's return range and MATCH's search range are entirely independent — they don't need to be part of the same left-to-right table structure.
How do you build a two-way lookup with INDEX/MATCH?
Use two MATCH functions inside one INDEX call — one to find the row position, one to find the column position.
What third argument should MATCH almost always use, and why?
0, for exact match, so it doesn't assume sorted data and silently return an approximate result.
How do you handle a lookup that might not find a match?
Wrap the entire INDEX/MATCH formula in IFERROR to display a custom message instead of #N/A.
Key takeaways
- INDEX returns a value at a position; MATCH returns a position for a value — combined, they form a full lookup.
- INDEX/MATCH can search in any direction, unlike VLOOKUP's left-to-right restriction.
- Two nested MATCH calls inside one INDEX create a two-way lookup on both rows and columns.
- Always use exact match (0) in MATCH unless deliberately doing bracket lookups.
- Header-based MATCH is more resilient to inserted/deleted columns than hardcoded indexes.
- INDEX/MATCH remains standard in legacy and finance models — worth mastering even in the XLOOKUP era.
- Wrap in IFERROR since neither function has built-in not-found handling.
Frequently asked questions
Is INDEX/MATCH obsolete now that XLOOKUP exists?
No — it does everything XLOOKUP does and remains extremely common in existing workbooks, especially in finance, so it's still essential to know.
Which is faster, INDEX/MATCH or VLOOKUP?
INDEX/MATCH is generally faster on large datasets because MATCH only searches one column instead of VLOOKUP scanning the whole table array internally.
Can INDEX/MATCH return an entire row instead of a single value?
Yes, omit the column argument in INDEX to return the whole matching row.
Does INDEX/MATCH work across different sheets?
Yes, both ranges can reference any sheet in the workbook, just like VLOOKUP and XLOOKUP.
Can I use INDEX/MATCH for partial text matches?
Yes, using wildcards like * or ? inside MATCH with match_type 0.
What's the biggest practical advantage over VLOOKUP?
The freedom to look up in any direction and build two-way lookups without rearranging your source data.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced