INDEX MATCH in Excel: Formula, Examples and Practice
Use INDEX and MATCH together to find a value in one column and return a matching value from another column. MATCH finds the row number. INDEX returns the cell at that row. The pair works in old and new versions of Excel.
The pattern looks like this:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Here is a quick example. A table lists products in A2:A6 and prices in C2:C6. To get the price of a chair:
=INDEX(C2:C6, MATCH("Chair", A2:A6, 0))The answer is 60. The 0 at the end tells MATCH to look for an exact match only.
Want to follow along? Download the free INDEX MATCH practice workbook with every sample table and the answers.
How do you use INDEX MATCH in Excel?
Use this sample table in A1:D6. The steps below use it.
| Product | Region | Price | Stock |
|---|---|---|---|
| Lamp | East | 25 | 12 |
| Lamp | West | 27 | 4 |
| Desk | East | 140 | 6 |
| Desk | West | 150 | 0 |
| Chair | East | 60 | 20 |
The goal is to find the price of the chair.
- Type the product name in a cell. Put Chair in F1.
- Find the row with MATCH. The formula
=MATCH(F1, A2:A6, 0)returns 5, because Chair is the fifth item in the list. - Return the price with INDEX. The formula
=INDEX(C2:C6, 5)returns 60. - Join the two. Put the MATCH formula where the 5 was:
=INDEX(C2:C6, MATCH(F1, A2:A6, 0)).
The result is 60. Change F1 to another product and the price changes with it.
Start both ranges on the same row and keep them the same size. If they do not line up, Excel returns the wrong row without any warning. For example, =INDEX(C2:C6, MATCH(F1, A3:A7, 0)) returns 150 instead of 60.
How do the two functions work together?
Each function does one small job.
- MATCH returns a position. For Chair in A2:A6, the position is 5.
- INDEX returns the cell at a given position in another range.
The position from MATCH becomes the row number for INDEX. That is all nesting means. MATCH sits inside INDEX, runs first, and hands its number over.
Explain it like I’m 5
Picture a row of lockers with names on the doors. MATCH walks down the row and says, “Chair is locker 5.” INDEX opens locker 5 in a second row, the one that holds prices, and hands you what is inside. MATCH finds the number. INDEX gets the thing.
How do you use INDEX MATCH with multiple criteria?
In the sample table, Desk appears twice. A one-condition lookup stops at the first Desk, which is East at 140. To get the West price, test two columns at once.
Each test gives TRUE or FALSE, and TRUE times TRUE equals 1. MATCH then looks for the 1.
=INDEX(C2:C6, MATCH(1, INDEX((A2:A6="Desk")*(B2:B6="West"), 0), 0))The result is 150. The inner INDEX(…, 0) saves you from pressing Ctrl+Shift+Enter in older versions. In Microsoft 365 you can leave it out. Add one more (range=value) part for each extra condition.
How do you use INDEX MATCH MATCH for a two-way lookup?
A two-way lookup finds a row and a column. Use a second table in A1:D4:
| Item | Jan | Feb | Mar |
|---|---|---|---|
| Lamp | 10 | 14 | 9 |
| Desk | 4 | 6 | 8 |
| Chair | 7 | 5 | 11 |
=INDEX(B2:D4, MATCH("Desk", A2:A4, 0), MATCH("Feb", B1:D1, 0))The result is 6. The first MATCH picks the row (Desk). The second picks the column (Feb). INDEX returns the cell where they meet. Swap the typed words for cell references to build a grid that changes when you type new names.
How do you look up a value to the left?
Say names sit in column A and IDs in column B:
| Name | ID |
|---|---|
| Ana | 101 |
| Ben | 102 |
| Cy | 103 |
=INDEX(A2:A4, MATCH(102, B2:B4, 0))The result is Ben. VLOOKUP needs the lookup value in the first column of its table, so it cannot return a column on its left. INDEX does not care where the return column sits.
INDEX MATCH vs VLOOKUP vs XLOOKUP: which should you use?
| Feature | VLOOKUP | XLOOKUP | INDEX with MATCH |
|---|---|---|---|
| Looks to the left | No | Yes | Yes |
| Default match type | Approximate, if you skip the last argument | Exact | MATCH assumes approximate, so type 0 |
| Insert a column in the table | You must fix the column number | Adjusts | Adjusts |
| Works in Excel 2016 and 2019 | Yes | No | Yes |
| Formula length | Short | Short | Longer |
Pick XLOOKUP when everyone who opens the file has a version that includes it. Microsoft says it is not in Excel 2016 or 2019. Pick INDEX with MATCH when older versions are in play, or when you need several values from one match. VLOOKUP is fine for a quick lookup when the key sits in the first column.
Speed rarely decides this. One expert test found VLOOKUP only about 5% faster.
INDEX with MATCH or FILTER?
INDEX with MATCH returns the first match only. FILTER returns every match and spills the results into nearby cells. For example, =FILTER(C2:C6, A2:A6="Lamp") returns 25 and 27. FILTER works in Excel 2021, 2024 and Microsoft 365, not in older versions. Use INDEX with MATCH for one value in any version. Use FILTER to list all matches.
How do you use IF with INDEX MATCH?
Wrap the lookup in IF to make a choice from the result:
=IF(INDEX(D2:D6, MATCH(F1, A2:A6, 0))>0, "In stock", "Sold out")With Chair in F1, this returns In stock. To show a clean message when the item is missing, use IFERROR:
=IFERROR(INDEX(C2:C6, MATCH("Sofa", A2:A6, 0)), "Not listed")This returns Not listed. IFERROR hides every error, including your own typos. IFNA hides only #N/A (Excel 2013 and later). To test whether an item exists, use =ISNUMBER(MATCH(F1, A2:A6, 0)).
Can you look up values in another sheet or in large data?
Yes. Put the sheet name before each range:
=INDEX(Prices!C2:C4, MATCH("Desk", Prices!A2:A4, 0))This returns 140 from a sheet named Prices.
For large tables, avoid repeating the search. If you need several columns for the same row, run MATCH once in a helper cell, then point each INDEX at it:
G2: =MATCH(F1, A2:A6, 0)
H2: =INDEX(C2:C6, G2)
I2: =INDEX(D2:D6, G2)The Excel speed guide at Decision Models suggests this for exact lookups that return many columns.
Why do you get #N/A, and how do you fix it?
| Cause | Fix |
|---|---|
| The value is not in the list, or is spelled differently | Check the spelling, or wrap the formula in IFERROR |
| Extra spaces, such as “Chair “ | Use TRIM, or CLEAN for hidden characters |
| A number stored as text, or the reverse | Make both sides the same data type |
| The last MATCH argument is missing | Type 0. Without it, MATCH assumes 1 and expects a list sorted from small to large |
| An array formula in an older Excel | Press Ctrl+Shift+Enter, or use the INDEX(…, 0) version shown above |
A wrong value with no error usually means the two ranges start on different rows. Start both on the same row.
Does it work in Google Sheets?
Yes. The basic pattern is the same: =INDEX(C2:C6, MATCH(F1, A2:A6, 0)).
Practice challenge
Use the first sample table (A1:D6). Try each task before you read the answer.
- Level 1: Find the stock of Chair.
- Level 2: Find the price of Lamp in the West.
- Level 3: Show “In stock” or “Sold out” for Desk in the West.
- Bonus: A table lists scores 0, 60, 70, 80, 90 in A2:A6 and grades F, D, C, B, A in B2:B6. Return the grade for a score of 85.
Answers
Level 1: =INDEX(D2:D6, MATCH("Chair", A2:A6, 0))
returns 20
Level 2: =INDEX(C2:C6, MATCH(1, INDEX((A2:A6="Lamp")*(B2:B6="West"), 0), 0))
returns 27
Level 3: =IF(INDEX(D2:D6, MATCH(1, INDEX((A2:A6="Desk")*(B2:B6="West"), 0), 0))>0, "In stock", "Sold out")
returns Sold out
Bonus: =INDEX(B2:B6, MATCH(85, A2:A6, 1))
returns BQuick answers
How do you get the last match instead of the first?
=INDEX(D2:D6, MATCH(2, INDEX(1/(A2:A6="Lamp"), 0)))This returns 4, the stock of the last Lamp row.
How do you match part of a word?
Use a wildcard with the 0 match type. =INDEX(C2:C6, MATCH("*air*", A2:A6, 0)) returns 60, the price of Chair. An asterisk stands for any run of characters. A question mark stands for one character.
How do you find the closest match?
Use 1 as the last argument and sort the lookup list from small to large. The bonus task above does this. MATCH returns the largest value that is less than or equal to your number.
Is MATCH case sensitive?
No. MATCH treats “chair” and “Chair” the same. For a case-sensitive lookup, add EXACT:
=INDEX(C2:C6, MATCH(TRUE, INDEX(EXACT(A2:A6, "Chair"), 0), 0))Do you still need this if you have XLOOKUP?
Not always. XLOOKUP is shorter. You will still meet this pair in older files and in files shared with people on Excel 2016 or 2019.
About this guide
Practice file: Download the free INDEX MATCH practice workbook with every sample table, every formula and the answers.
Last updated: October 20, 2026. Every formula above was tested in Excel on the sample tables shown, and the results match what is written.