INDEX/MATCH is the lookup that does everything VLOOKUP can’t — look to the left, survive inserted columns, and match on two dimensions at once. It looks intimidating because it’s two functions working together, but each does one simple job: MATCH finds where a value is, and INDEX returns what’s at a position. Understand them separately and the combination clicks. This walks through each function, how they pair, and exactly why analysts reach for INDEX/MATCH on serious spreadsheets.
MATCH: find the position
MATCH answers “where is this?” — it searches a range and returns the position number of a value, not the value itself.
=MATCH("France", A2:A10, 0) 'returns 4 if France is the 4th itemThe three arguments are the value to find, the range to search, and a match type — almost always 0 for an exact match. So if “France” is the fourth entry in A2:A10, MATCH returns 4. On its own that number seems useless, but it’s the key to the whole technique: MATCH locates things by position, and position is exactly what INDEX needs. Think of MATCH as the part that does the searching. The third argument is worth getting right — 0 means exact match (what you want almost always), while 1 and -1 do approximate matches on sorted data. When a MATCH returns the wrong position or an #N/A, a missing or wrong third argument is the usual cause, just as it is with VLOOKUP’s final argument.
INDEX: return by position
INDEX is the mirror image — it answers “what’s at this position?” Give it a range and a number, and it returns the value sitting there.
=INDEX(C2:C10, 4) 'returns the 4th value in column CBy itself, hard-coding the 4 isn’t useful — you’d have to know the position in advance. But notice the shape of the problem: INDEX needs a position number, and MATCH produces exactly that. INDEX does the retrieving, MATCH does the finding, and neither is much use alone. Put them together and you have a lookup.
Combine them into a lookup
Nest the MATCH inside the INDEX — MATCH finds the row number, INDEX returns the value from that row of your chosen column.
=INDEX(C2:C10, MATCH("France", A2:A10, 0))Read it inside-out: MATCH finds that “France” is in position 4 of column A, and INDEX returns the 4th value of column C — France’s data. That’s a complete lookup, doing the same job as VLOOKUP but built from two transparent pieces. The pattern is always the same: =INDEX(column-you-want, MATCH(value-you-have, column-to-search-in, 0)). Once that template is in your fingers, you can write any lookup without thinking about column-index numbers ever again. Microsoft’s guide to lookup functions covers the variations. The mental model that makes it stick: you’re not learning a “lookup function,” you’re composing two simple ones. MATCH always answers “which position?” and INDEX always answers “what’s at that position?” — and once you see every lookup as those two questions, the syntax stops being something to memorize and becomes something you reason through.
Why it beats VLOOKUP
INDEX/MATCH isn’t just an alternative to VLOOKUP — it fixes two real weaknesses that bite on larger spreadsheets.
| VLOOKUP problem | INDEX/MATCH |
|---|---|
| Can only return a column to the right of the key | Looks any direction — key and result can be any columns |
| Breaks when you insert/delete a column (fixed index number) | References columns directly, so it survives column changes |
| Re-reads the whole table width | Reads only the two columns it needs |
The look-left ability alone solves problems VLOOKUP simply can’t — when your key sits to the right of the value you want, INDEX/MATCH handles it without rearranging the data. And the column-insertion resilience matters enormously on a sheet other people edit: a VLOOKUP with a hard-coded 3 silently returns the wrong column the moment someone inserts a column before it, while INDEX/MATCH keeps pointing at the right data because it names the column directly. On any spreadsheet that lives and grows, that robustness is worth the slightly longer formula. There’s a performance angle too, though it only matters on big data: VLOOKUP scans across the whole table width to reach its column, while INDEX/MATCH touches only the lookup column and the return column. On a few hundred rows you’ll never notice; on tens of thousands with many columns, INDEX/MATCH can recalculate noticeably faster. For most people the deciding factors are looking left and surviving column edits — but on heavy workbooks, the speed is a real bonus.
The two-way lookup
Here’s where INDEX/MATCH pulls decisively ahead: use two MATCH functions and you can find a value by row and column at once — a matrix lookup VLOOKUP can’t do.
=INDEX(B2:M10, MATCH("France", A2:A10, 0), MATCH(2026, B1:M1, 0))INDEX takes an optional third argument — a column number — so the first MATCH finds the row (France) and the second finds the column (2026), and INDEX returns the cell where they intersect. For a grid of data — population by country and year, price by product and region — this is the clean answer, the same two-way lookup that would otherwise need a clumsy nested VLOOKUP. Two MATCHes, one INDEX, any cell in a table.
Match on multiple criteria
One more trick puts INDEX/MATCH ahead of a basic VLOOKUP: matching on two or more conditions at once, with no helper column. The key is giving MATCH an array of TRUE/FALSE values to search.
=INDEX(D2:D100, MATCH(1, (A2:A100="North")*(B2:B100="Widget"), 0))The multiplication (A2:A100="North")*(B2:B100="Widget") produces a column of 1s and 0s — 1 only where both conditions are true — and MATCH finds the first 1, which INDEX then returns. So you can look up “the North region’s Widget price” on two criteria in a single formula. In older Excel this needs Ctrl+Shift+Enter to confirm as an array; in 365 it just works. It’s the kind of lookup VLOOKUP can’t do without building a concatenated helper column first, and it’s a genuine reason to reach for INDEX/MATCH on complex data.
INDEX/MATCH or XLOOKUP?
If you have a recent Excel, XLOOKUP does most of what INDEX/MATCH does with simpler syntax — it looks any direction and survives column changes too.
So why learn INDEX/MATCH? Two reasons. First, compatibility: INDEX and MATCH work in every version of Excel ever made, while XLOOKUP needs 365 or 2021 — so for a workbook shared with older versions, INDEX/MATCH is the safe choice. Second, it’s everywhere in existing spreadsheets, so you have to read it regardless. For brand-new work on modern Excel, XLOOKUP is usually the easier call; but INDEX/MATCH remains the most portable advanced lookup, and understanding it — MATCH finds the position, INDEX returns the value — makes you fluent in how lookups actually work under the hood. Learn the template once — INDEX the column you want, MATCH the value you have — and no lookup a spreadsheet throws at you, in any version of Excel, will be out of reach.