INDEX and MATCH are two small functions that combine into one flexible lookup. MATCH finds which row a value sits in; INDEX returns the value from that row, in any column you point at. People keep using the pair for two reasons: it works in every version of Excel, and it can look in any direction, including to the left, where VLOOKUP cannot go.
The combined formula looks like this: =INDEX(C2:C5, MATCH(D2, A2:A5, 0)). Read it inside out. MATCH finds the position of D2 in column A, and INDEX returns the cell at that position in column C. The rest of this guide builds that up one function at a time, then covers left lookups, multiple criteria, and the errors that catch people.
How does INDEX MATCH work?
The trick to understanding the combination is to stop reading it as one formula. It’s two, and each does one small job.
Step 1: MATCH finds the position
MATCH searches a range for a value and returns where it is, as a number counted from the top. Not the value, just the position.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Find SKU | Position |
| 2 | S-101 | Stapler | 4.10 | S-103 | 3 |
| 3 | S-102 | Tape | 2.80 | ||
| 4 | S-103 | Binder | 7.25 | ||
| 5 | S-104 | Scissors | 5.60 |
The 0 at the end is the match type, and it means exact match. It’s the INDEX/MATCH equivalent of VLOOKUP’s FALSE, and just as easy to forget, with the same consequences. More on that in the troubleshooting section.
Step 2: INDEX returns the value at a position
INDEX does the opposite job. Give it a range and a position, and it hands back the value sitting there:
The third cell in C2:C5 is C4, so this returns 7.25, the Binder’s price. On its own that isn’t much of a lookup, because you had to know the position was 3.
Step 3: put the MATCH inside the INDEX
But you have a function whose whole job is producing that number. Swap the hard-coded 3 for the MATCH:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Find SKU | Price |
| 2 | S-101 | Stapler | 4.10 | S-103 | 7.25 |
| 3 | S-102 | Tape | 2.80 | ||
| 4 | S-103 | Binder | 7.25 | ||
| 5 | S-104 | Scissors | 5.60 |
That’s the whole pattern. One range to search, one range to return from, and a 0 for an exact match. The two ranges must cover the same rows (A2:A5 and C2:C5 both span rows 2 to 5); if they’re offset, the position lands in the wrong row of the return range and you get a plausible, wrong answer.
How do I look up to the left with INDEX MATCH?
Because the search range and the return range are separate arguments, nothing forces the answer to sit to the right of the value you look up. Say you have a product name and want its SKU, using the same table as above. The names are in column B, and the SKUs sit to their left in column A:
MATCH finds Binder at position 3 in B2:B5, and INDEX returns the third cell of A2:A5: S-103.
VLOOKUP cannot do this at all. Microsoft’s own guidance puts it plainly: “the VLOOKUP function can only look up a value from left to right.” For years, this one limitation was the main reason people learned INDEX/MATCH, and it still comes up in any workbook where the key column didn’t happen to land on the left edge.
How do I use INDEX MATCH with multiple criteria?
A plain MATCH searches one column. To match on two or more conditions at once, the standard pattern compares each column to its criterion, multiplies the results, and asks MATCH to find a 1:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Quarter | Sales | Find region | Find quarter | Sales |
| 2 | North | Q1 | 8,200 | South | Q1 | 7,400 |
| 3 | North | Q2 | 9,100 | |||
| 4 | South | Q1 | 7,400 | |||
| 5 | South | Q2 | 6,900 |
One caveat, straight from Microsoft’s page on INDEX/MATCH errors: “When you use an array in INDEX, MATCH, or a combination of those two functions, it is necessary to press Ctrl+Shift+Enter on the keyboard.” That applies to Excel versions without dynamic arrays. In current Microsoft 365, you just press Enter and the formula works as a dynamic array formula; in older versions it has to be entered as a legacy array formula with that keystroke instead.
The same boolean-multiplication idea works in XLOOKUP on current Excel, with no special keystroke. If you’d rather avoid array thinking entirely, a helper column that concatenates the keys (=A2&B2) plus a single-criterion MATCH on it does the same job with less cleverness to maintain.
INDEX MATCH vs VLOOKUP vs XLOOKUP
All three do the same core job. The differences are in what they tolerate:
| VLOOKUP | INDEX/MATCH | XLOOKUP | |
|---|---|---|---|
| Where it can look | leftmost column only | any column, either direction | any column, either direction |
| How it returns a value | a column number you count | a return range you point at | a return array you select |
| Default match | approximate | approximate (add the 0) |
exact |
| No match found | #N/A (wrap in IFNA) |
#N/A (wrap in IFNA) |
built-in if_not_found argument |
| Survives an inserted column | no | yes | yes |
| Works in every Excel version | yes | yes | no (Microsoft 365 / 2021 and later) |
The honest ranking: on current Excel, XLOOKUP is the better formula. It reads left to right, matches exactly by default, and handles a miss without a wrapper. Microsoft’s XLOOKUP page states the compatibility line outright: “XLOOKUP is not available in Excel 2016 and Excel 2019.”
That last sentence is INDEX/MATCH’s whole case. It runs in any version of Excel anyone on your team might have, which matters the moment a workbook leaves your machine. It also shares XLOOKUP’s structural advantage over VLOOKUP: because it points at ranges instead of counting columns, inserting a column into the table doesn’t silently redirect the lookup.
The VLOOKUP-to-XLOOKUP decision gets its own treatment in VLOOKUP vs XLOOKUP: which should you use?, and the full XLOOKUP tour is in the XLOOKUP guide.
Why is my INDEX MATCH not working?
Almost every broken INDEX/MATCH comes down to one of five causes.
1. The missing 0
MATCH’s third argument defaults to 1, an approximate match that finds the largest value less than or equal to your lookup value and assumes the range is sorted in ascending order. On unsorted data that returns #N/A or, quietly worse, the wrong position. Always write the 0 unless you specifically want the approximate behavior.
2. Mismatched data types
The number 1005 will not match the text "1005", even though the cells look identical. Microsoft’s INDEX/MATCH error page calls out cells formatted as text as a standard cause. Convert one side so both are the same type.
3. Stray spaces and hidden characters
A value pasted from another system with a trailing space is, to MATCH, a different value. Clean it with TRIM. Case, on the other hand, is never the culprit: MATCH does not distinguish between uppercase and lowercase letters when matching text.
4. Misaligned or mismatched ranges
If the MATCH range and the INDEX range don’t start on the same row, the answer is off by the difference. And if the MATCH range is longer than the INDEX range, a position near the end points outside it, which INDEX reports as #REF!, since its row number must point to a cell within the range it was given. What each error code means, and which ones matter, is covered in Excel’s error values, explained.
5. A genuinely missing value
Sometimes #N/A is the correct answer: the value isn’t there. When a miss is expected, wrap the formula in IFNA, which returns your fallback only for #N/A and lets real errors through: =IFNA(INDEX(C2:C5, MATCH(D2, A2:A5, 0)), "Not found"). The same type and space mismatches break VLOOKUP too, and why your VLOOKUP returns #N/A walks through each fix in more detail.
The short version
MATCH finds the position, INDEX returns the value at it, and the 0 keeps the match exact. MATCH used on its own, with its full match_type, wildcards, and the newer XMATCH, has its own guide. Keep the two ranges covering the same rows, and the pair will do left lookups, multi-criteria lookups, and survive inserted columns, in any version of Excel ever likely to open your file. On current Excel, reach for XLOOKUP first; reach for INDEX/MATCH when compatibility matters or when you’re maintaining the thousands of workbooks already built on it. And if you rework the lookups in a model other people rely on, put the old and new versions side by side and confirm the numbers stayed put while the formulas changed.
