INDEX MATCH looks up a value by combining two functions: MATCH finds the position of your lookup value in one column, and INDEX returns the cell at that position from another column. The formula is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). So =INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0)) finds the code from A2 in the Prices code column and returns the price from the same row. The 0 in MATCH forces an exact match, and it is the piece you never leave out.
You use INDEX MATCH for the same reason you use any lookup: two sheets need to agree. After you convert a PDF report into Excel, a column of codes or references needs names, categories, or prices attached from a master list. INDEX MATCH does that job, and it does two things VLOOKUP cannot, which is why many finance teams standardize on it.
How the two functions fit together
Read the formula from the inside out. MATCH runs first and answers "which row?", then INDEX uses that row number to pull the value.
| Part | What it does | Example |
|---|---|---|
| MATCH(lookup_value, lookup_range, 0) | Returns the position of the value in a column, exact match | MATCH(A2, Prices!A:A, 0) |
| INDEX(return_range, position) | Returns the cell at that position in another column | INDEX(Prices!B:B, 4) |
The third argument of MATCH is the exact-match switch. Use 0 for an exact match, which is what you want almost every time. A 1 or -1 looks for an approximate match and requires sorted data, and leaving it out defaults to 1, which is how people get a real value back that is quietly the wrong row.
A worked example
Say you converted a purchase report and landed with item codes in column A, and you keep a price list on a sheet named Prices with codes in column A and prices in column B. In C2 you write =INDEX(Prices!$B$2:$B$500, MATCH(A2, Prices!$A$2:$A$500, 0)) and fill it down. MATCH finds the code from A2 in the Prices code column and returns its row position, then INDEX hands back the price from that row of the price column. Lock both ranges with dollar signs so they hold steady as the formula copies down.
Why use INDEX MATCH instead of VLOOKUP?
INDEX MATCH beats VLOOKUP on two points that bite real spreadsheets. First, it can look to the left: the lookup column and the return column are separate ranges, so the value you search does not have to sit to the left of the value you return. VLOOKUP cannot do that. Second, it survives structural changes. VLOOKUP returns a hard-coded column number, so inserting a column in the middle of your table shifts the answer without warning. INDEX MATCH points at named ranges, so an inserted column moves with it. The cost is that the formula is longer and takes a minute to read. On Microsoft 365 and Excel 2021, XLOOKUP gives you the same two advantages in a shorter formula, so INDEX MATCH is most valuable when you need those benefits on Excel 2019 or older, where XLOOKUP does not exist.
| INDEX MATCH | VLOOKUP | XLOOKUP | |
|---|---|---|---|
| Looks left | Yes | No | Yes |
| Survives an inserted column | Yes | No | Yes |
| Works in Excel 2019 and older | Yes | Yes | No |
| Formula length | Long | Short | Short |
How do I do a two-way lookup with INDEX MATCH?
Use two MATCH functions, one for the row and one for the column, inside a single INDEX. To pull the value where a row label and a column label cross, write =INDEX(B2:F100, MATCH(H2, A2:A100, 0), MATCH(H3, B1:F1, 0)). The first MATCH finds which row H2 sits in, the second finds which column H3 sits in, and INDEX returns the cell where they meet. This is the classic grid lookup, useful when a converted report has both a category down the side and a month across the top.
How do I match on two criteria?
Concatenate the criteria inside MATCH so it searches for both at once: =INDEX(C2:C500, MATCH(H2&H3, A2:A500&B2:B500, 0)). This looks up the row where column A equals H2 and column B equals H3 together, which is how you find one transaction identified by, say, both a date and an account. In Excel 2019 and earlier, enter it with Ctrl+Shift+Enter as an array formula; in 365 and 2021 a normal Enter is enough.
Using MATCH on its own
MATCH is useful by itself when you want a position rather than a value. =MATCH("Total", A1:A500, 0) tells you which row holds the word Total, which is handy for locating the summary line a conversion buried somewhere in a long sheet. You can also feed that position into other functions, or use MATCH to make a lookup adjust automatically: replace a hard-coded column number with MATCH("Amount", A1:F1, 0) so the formula always points at the Amount column even if it moves. That trick is what lets an INDEX MATCH keep working after you rearrange the source layout.
A left lookup, which VLOOKUP cannot do
Here is the case that sends people to INDEX MATCH in the first place. Your converted sheet has names in column C and the account IDs you want in column A, so the value you have sits to the right of the value you need. VLOOKUP is stuck, because it only returns from columns to the right of the search column. INDEX MATCH does not care about order: =INDEX(A2:A500, MATCH("Acme Corp", C2:C500, 0)) searches column C for the name and returns the ID from column A on the same row. The lookup column and the return column are independent, so left, right, or ten columns apart makes no difference to the formula.
Why is my INDEX MATCH returning #N/A?
#N/A means MATCH could not find the lookup value, and on converted data the formula is rarely the culprit. The leading cause is numbers stored as text: the code in A2 is text while the master code is a real number, so MATCH sees no match; fix the type with our guide to converting text to numbers. Next is trailing spaces left by the extraction, which you clear by removing extra spaces before you compare. A #REF! error, by contrast, usually means INDEX was handed a position outside its range, so check that your return range is as tall as the data.
Where INDEX MATCH fits in a conversion workflow
The sequence is familiar. You convert the PDF to Excel, you are left with codes that carry no meaning, and INDEX MATCH attaches the name, category, or price each one stands for. When the source is a stack of invoices, it is faster to pull each line item into a spreadsheet automatically than to key them by hand and hope the references align. After the lookup runs clean, compare the two columns to confirm every code found a match, because a lookup that returns #N/A on a third of the rows is a data problem, not a finished report.
The short version
Use =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), always with the 0 for an exact match, and lock both ranges with dollar signs. It looks left and survives inserted columns, which makes it the reliable choice on Excel 2019 and older where XLOOKUP is not available. If you get #N/A on converted data, check for numbers stored as text and stray spaces before you doubt the formula.