INDEX MATCH
Excel Guides / Lookup & Reference
Lookup & Reference
Pair two functions into a lookup that looks left, survives inserted columns and works in every version of Excel.
Updated 12 Oct 2026 Ā· 6 min read Ā· By Sky Dashboards
INDEX MATCH is not one function but two working together. MATCH finds the position of a value in a list, and INDEX returns whatever sits at that position in another list. Together they do everything VLOOKUP does, plus several things it cannot, and they work in every version of Excel. That makes the pair the safe choice for workbooks shared with people on older versions.
In this guide
The INDEX function
INDEX returns the value at a given row and column of a range. It does not search for anything. You tell it where to look.
=INDEX(array, row_num, [column_num])| Argument | Required? | What it does |
|---|---|---|
array |
Required | The range to return a value from. |
row_num |
Required | Which row of array to return, counting from 1 at the top of the range, not from row 1 of the sheet. |
column_num |
Optional | Which column of array to return. Needed when array is more than one column wide and you want a single cell. |
=INDEX(C2:C6,3)Returns the third value in C2:C6, which is the value in C4.
On its own that is not much use, because you rarely know the position in advance. Finding it is MATCH's job.
The MATCH function
MATCH searches a single row or column and returns the position of the value it finds, not the value itself.
=MATCH(lookup_value, lookup_array, [match_type])| Argument | Required? | What it does |
|---|---|---|
lookup_value |
Required | The value to find. |
lookup_array |
Required | The single row or column to search. |
match_type |
Optional |
0 for an exact match, 1 for the largest value less than or equal to lookup_value, -1 for the smallest value greater than or equal to it. Leave it out and Excel uses 1. |
| match_type | Finds | Data must be |
|---|---|---|
| 0 | The first value exactly equal to lookup_value | In any order |
| 1 (default) | The largest value less than or equal to lookup_value | Sorted ascending |
| -1 | The smallest value greater than or equal to lookup_value | Sorted descending |
Always type the 0
The default match_type is 1, not 0. Leave it out on an unsorted list and MATCH returns a wrong position with no error. For ordinary lookups, end every MATCH with ,0).
=MATCH("Grace Tan",B2:B5,0)Returns 3, because Grace Tan is the third name in B2:B5.
Combine INDEX and MATCH
Put MATCH inside INDEX as the row number. MATCH finds the row, and INDEX returns the value from that row of the column you want. Here a fitness coach looks up a client's ID from their name:
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Client ID | Client | Plan | Fee | Client | Client ID | ||
| 2 | C-201 | Hannah Lee | Starter | $120 | Grace Tan | C-203 | ||
| 3 | C-202 | Omar Farouk | Pro | $240 | ||||
| 4 | C-203 | Grace Tan | Pro | $240 | ||||
| 5 | C-204 | Liam Ross | Elite | $380 |
MATCH finds Grace Tan at position 3 of B2:B5, and INDEX returns position 3 of A2:A5.
Read it from the inside out: find F2 among the names, then take the ID at the same position. The two ranges must be the same height and start on the same row, or the positions will not line up.
=INDEX(D2:D5,MATCH(F2,B2:B5,0))Change the first range to return the fee instead: $240 for Grace Tan.
Why INDEX MATCH beats VLOOKUP
- It can look left. The ID in the example sits to the left of the names. VLOOKUP cannot return it without rearranging the table.
- Inserted columns do not break it. VLOOKUP picks its column with a typed number, so inserting a column inside the table makes it return the wrong one. INDEX MATCH points at real ranges, which Excel adjusts for you.
- It works across as well as down. MATCH can search a row as easily as a column, so the same pattern handles horizontal tables that would otherwise need HLOOKUP.
- It works everywhere. INDEX and MATCH have been in Excel for decades, so the formula behaves the same in Excel 2010 as in Microsoft 365.
The cost is a longer formula with two sets of brackets. If everyone using the file has Microsoft 365 or Excel 2021 or later, XLOOKUP does the same job in one function.
Two-way lookup with INDEX MATCH MATCH
INDEX accepts a row and a column, so give it a MATCH for each. One finds the row, the other finds the column, and INDEX returns the cell where they cross. Here an online shop looks up a delivery rate by zone and parcel size:
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | Zone | Small | Medium | Large | |
| 2 | Metro | $9.00 | $12.00 | $16.00 | |
| 3 | Regional | $11.00 | $15.00 | $21.00 | |
| 4 | Remote | $15.00 | $22.00 | $30.00 | |
| 5 | |||||
| 6 | Zone | Size | Rate | ||
| 7 | Regional | Large | $21.00 |
Regional is row 2 of the rate grid and Large is column 3, so INDEX returns the value in D3.
The row MATCH searches the labels down the side and the column MATCH searches the headers across the top. Both label ranges must line up with the edges of the INDEX range: A2:A4 beside B2:D4, and B1:D1 above it.
Tip
Set column_num to 0 and INDEX returns the whole row instead of one cell. =INDEX(B2:D4,MATCH(A7,A2:A4,0),0) returns all three Regional rates, which spill across in Microsoft 365 and Excel 2021 or later.
INDEX MATCH with multiple criteria
When one column is not enough to identify a row, such as a garment that comes in several sizes, match on two conditions at once. Multiply the tests together and look for the 1:
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Size | Price | Item | Size | Price | ||
| 2 | Tee | S | $18.00 | Hoodie | L | $42.00 | ||
| 3 | Tee | M | $18.00 | |||||
| 4 | Tee | L | $20.00 | |||||
| 5 | Hoodie | S | $38.00 | |||||
| 6 | Hoodie | M | $38.00 | |||||
| 7 | Hoodie | L | $42.00 |
Only row 7 is both a Hoodie and size L, so MATCH returns position 6 and INDEX returns $42.00.
Each comparison returns a list of TRUE and FALSE values. Multiplying them turns those into 1s and 0s, and a row scores 1 only when every condition is true. MATCH then finds the first 1. Add more conditions by multiplying in more brackets.
Older versions of Excel
In Microsoft 365 and Excel 2021 or later you press Enter as normal. In Excel 2019 and earlier this is an array formula, so confirm it with Ctrl + Shift + Enter and Excel shows curly braces around it in the formula bar. Press plain Enter there and you get an error or a wrong result.
To avoid the special keystroke in old versions, wrap the multiplied tests in an extra INDEX, which makes Excel treat them as an array:
=INDEX(C2:C7,MATCH(1,INDEX((A2:A7=E2)*(B2:B7=F2),0),0))Same result, $42.00, confirmed with a plain Enter in any version.
XMATCH: the modern partner for INDEX
Microsoft 365, Excel 2021 and later also have XMATCH, an updated MATCH with the same options as XLOOKUP. Its default is an exact match, so you no longer need the trailing 0.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | Required? | What it does |
|---|---|---|
lookup_value |
Required | The value to find. |
lookup_array |
Required | The row or column to search. |
match_mode |
Optional |
0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. |
search_mode |
Optional |
1 first to last (the default), -1 last to first, 2 or -2 for a binary search on data sorted ascending or descending. |
=INDEX(A2:A5,XMATCH(F2,B2:B5))The client ID lookup from earlier, written with XMATCH. It returns C-203.
XMATCH also finds the next smaller or larger value in an unsorted list, and can search from the bottom up, neither of which MATCH can do. Use it when everyone has a current version and you like the control INDEX gives you.
Frequently asked questions
Is INDEX MATCH faster than VLOOKUP?
On the size of sheet most small businesses use, you will not notice a difference. Choose INDEX MATCH for its flexibility, not for speed.
Why does my INDEX MATCH return the wrong value?
Check for a missing 0 at the end of MATCH, which makes it do an approximate match on unsorted data. Then check that the INDEX range and the MATCH range start and end on the same rows.
Can INDEX MATCH return a whole row?
Yes. Set column_num to 0, as in =INDEX(B2:D5,MATCH(F2,A2:A5,0),0). In Microsoft 365 and Excel 2021 or later the row spills across.
Should I use INDEX MATCH or XLOOKUP?
If every user has Microsoft 365 or Excel 2021 or later, XLOOKUP is shorter and easier to read. If the file goes to people on older versions, INDEX MATCH is the safe choice.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā