INDEX MATCH in Excel

INDEX and MATCH are two separate, fairly plain functions that become genuinely powerful the moment you combine them. On its own, INDEX(array, row_num) just answers “what's in position N”; it doesn't search for anything. MATCH(lookup_value, lookup_array) does the searching, but it only ever returns a position, never the actual value. Put them together and MATCH supplies the position INDEX needs, and you've built a lookup with none of VLOOKUP's structural limits.

Read it from the inside out: MATCH finds where the lookup value sits in lookup_range; INDEX then goes to that same position in return_range and returns what's there. Critically, return_range and lookup_range don't have to be part of the same table, or even in the same relative position, which is exactly what VLOOKUP can't do.

INDEX syntax

MATCH syntax

The mistake almost everyone makes

Mixing up which function does what, or writing MATCH's three arguments in the wrong order inside INDEX. The pattern that goes wrong most often is forgetting MATCH's third argument, 0, for an exact match. Without it, MATCH quietly does an approximate match on data that usually isn't actually sorted for one, and INDEX then confidently returns the value at the wrong position with no error at all.

That trailing 0 inside MATCH is the single most important character in the whole formula.

A real example: looking up a value to the LEFT of the lookup column

Say you have a position report where the ticker sits in column C, but the exchange code you need is in column A, to the left. VLOOKUP can't reach it; it only ever searches rightward from its first column. INDEX/MATCH doesn't care which side anything is on:

This finds the row where the ticker in D2 matches column C, then returns the exchange code from the same row in column A: a plain reverse lookup that would otherwise mean rearranging the whole report just to satisfy VLOOKUP's first-column rule. This is also the version of the lookup question interviewers ask when they want to see whether you understand why INDEX/MATCH exists, not just that it does.

INDEX MATCH MATCH: two-way lookup

When the value you want sits at the crossing of a row and a column, give INDEX two MATCHes: one finds the row, the other finds the column. This is the classic matrix lookup, and the one interviewers like to ask for.

The first MATCH returns 2 (South is the second region), the second returns 2 (Q2 is the second quarter), so INDEX returns the value in row 2, column 2 of B2:D4. Replace the text with cell references, for example MATCH(G1, A2:A4, 0), and you have a small lookup tool that answers any region and quarter.

INDEX MATCH between two sheets

Nothing changes except the sheet name in front of each range. The formula can live on any sheet; the lookup range and the return range just need to line up row for row:

The easiest way to build it: type =INDEX(, click the Prices tab, select column C, and Excel writes Prices!C:C for you. If the sheet name has spaces, Excel adds quotes: 'Price List'!C:C.

Find the last match (search from the bottom up)

MATCH with 0 always returns the first match. To get the most recent entry, for example a customer’s latest order, use this pattern:

1/(A2:A6="Acme") turns every Acme row into 1 and every other row into an error. MATCH then looks for 2, which never appears, so it settles on the last 1 it can find. It works natively in Microsoft 365 and Excel 2021; in older versions confirm it with Ctrl+Shift+Enter. In Microsoft 365 and Excel 2021 the same result is simpler with XLOOKUP’s search mode: =XLOOKUP("Acme", A2:A6, C2:C6, , 0, -1).

Find the 2nd, 3rd or nth match with SMALL

IF returns the position of every Acme row (1, 3 and 5), SMALL picks the 2nd smallest (3), and INDEX returns the third amount: 180. Change the last number to get the 3rd match. Like the last-match formula, it needs Ctrl+Shift+Enter in Excel 2019 and earlier. In Microsoft 365 and Excel 2021, =FILTER(C2:C6, A2:A6="Acme") returns all of them at once.

INDEX MATCH with multiple criteria

Each condition produces TRUE or FALSE for every row; multiplying them gives 1 only where both are true. MATCH finds that 1 (row 4) and INDEX returns 320. Add more conditions by multiplying more brackets. Same note on Ctrl+Shift+Enter for older versions.

INDEX MATCH vs VLOOKUP vs XLOOKUP

If everyone who opens your file has Microsoft 365, XLOOKUP is the simplest. If the file goes to clients, an employer’s test machine or anyone on an older version, INDEX MATCH is the alternative to VLOOKUP that works everywhere.

Worked examples

Common mistakes

• Confusing row_num with the actual row number on the sheet. It's relative to the range, not the sheet.
• Leaving off col_num on a multi-column range. INDEX defaults to column 1, which is easy to miss.
• Using INDEX alone when what you actually need is INDEX + MATCH together.
• Forgetting the 0 for exact match. Omitting it silently switches to approximate matching on sorted data.
• Expecting MATCH to return the value. It only ever returns a position.
• Not wrapping in IFERROR or IFNA when the value might genuinely be missing.
• Giving INDEX and MATCH ranges of different sizes or starting rows, for example INDEX(C2:C100, MATCH(x, A1:A100, 0)). Every result is then off by one row.

Related functions

• XLOOKUP
• VLOOKUP
• HLOOKUP