Excel practice › Excel functions › INDEX MATCH
INDEX MATCH in Excel: how the two functions work together
INDEX and MATCH are two functions used as one lookup: MATCH finds the position of a value in a column, and INDEX returns what sits at that position in another column. To get the customer on invoice 4075, with invoice numbers in C2:C7 and customers in A2:A7: =INDEX(A2:A7, MATCH(4075, C2:C7, 0)).
The two ranges are separate, so the answer can come from a column to the left of the one you search, which VLOOKUP can't do. Add a second MATCH for the column and you have a two-way lookup. Both functions work in Excel 2016 and 2019, which don't have XLOOKUP.
Syntax
=INDEX(array, row_num, [column_num]) · =MATCH(lookup_value, lookup_array, [match_type])
array- INDEX: the range to return from. One column for a plain lookup, a block of several columns for a two-way lookup.
row_num- INDEX: which row of
arrayto return, counted from its first row. This is where the MATCH goes. column_num- INDEX: which column of
array, counted from its first column. Optional whenarrayis a single column. lookup_value- MATCH: what to look for, a value or the cell that holds it.
lookup_array- MATCH: the one column (or row) to search. The result is a position inside this range, not a sheet row number.
match_type- MATCH:
0= exact match.1, or nothing at all, = the largest value that is less than or equal to the lookup value, which needs the range sorted ascending.-1= the smallest value that is greater or equal, on a range sorted descending. For IDs and names, always write0.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Amount | Invoice | Find invoice | Customer | |
| 2 | Harbor Dental | 1250 | 4071 | 4075 | Summit Yoga | |
| 3 | Pine St Bakery | 480 | 4072 | |||
| 4 | Lakeside Auto | 2310 | 4074 | |||
| 5 | Summit Yoga | 640 | 4075 | |||
| 6 | Ortiz Plumbing | 1875 | 4077 | |||
| 7 | Maple Vet | 920 | 4078 |
A2:A7 to B2:B7 for the amount.How Excel reads it
- The inner function runs first.
MATCH(E2,C2:C7,0)looks for 4075 in C2:C7 and finds it in the 4th cell, so it returns 4. MATCH gives a position, not a value. - The formula is now
=INDEX(A2:A7,4): the 4th cell of A2:A7, which is Summit Yoga.
If the value appears twice, MATCH returns the position of the first one. Upper and lower case don't matter to MATCH. When it finds nothing it returns #N/A, and so does the whole formula.
The two ways it returns the wrong row
Both give an answer that looks fine and isn't. They are live below.
1. The two ranges don't start on the same row. MATCH counts from the first cell of its range, and INDEX counts from the first cell of its own. If one range includes the header and the other doesn't, every answer is off by one row:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Amount | Invoice | Find invoice | Customer | |
| 2 | Harbor Dental | 1250 | 4071 | 4075 | Lakeside Auto | |
| 3 | Pine St Bakery | 480 | 4072 | |||
| 4 | Lakeside Auto | 2310 | 4074 | |||
| 5 | Summit Yoga | 640 | 4075 | |||
| 6 | Ortiz Plumbing | 1875 | 4077 | |||
| 7 | Maple Vet | 920 | 4078 |
A1:A7 to A2:A7.2. The 0 is missing from MATCH. Without a third argument MATCH does an approximate match: when the value isn't there, it settles for the largest one below it. Invoice 4076 doesn't exist, yet this formula returns a customer:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Amount | Invoice | Find invoice | Customer | |
| 2 | Harbor Dental | 1250 | 4071 | 4076 | Summit Yoga | |
| 3 | Pine St Bakery | 480 | 4072 | |||
| 4 | Lakeside Auto | 2310 | 4074 | |||
| 5 | Summit Yoga | 640 | 4075 | |||
| 6 | Ortiz Plumbing | 1875 | 4077 | |||
| 7 | Maple Vet | 920 | 4078 |
,0 inside MATCH and you get the honest answer: #N/A, there is no invoice 4076.One more error to know: when the row or column number points outside the range, INDEX returns #REF!. That happens when the INDEX range is shorter than the MATCH range.
A two-way lookup
Give INDEX a block of several columns and it needs two numbers: a row and a column. Use one MATCH for each. Here the first MATCH finds the package size down column A and the second finds the zone across row 1:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Size | Zone 1 | Zone 2 | Zone 3 | Size | Zone | Rate | |
| 2 | Envelope | 6 | 8 | 11 | Medium box | Zone 3 | 23 | |
| 3 | Small box | 9 | 12 | 16 | ||||
| 4 | Medium box | 13 | 17 | 23 | ||||
| 5 | Large box | 19 | 25 | 34 | ||||
| 6 | Pallet | 85 | 110 | 140 |
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
Someone types an invoice number in E2. In F2, return the amount of that invoice with INDEX and MATCH.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Amount | Invoice | Find invoice | Amount | |
| 2 | Harbor Dental | 1250 | 4071 | 4077 | ||
| 3 | Pine St Bakery | 480 | 4072 | |||
| 4 | Lakeside Auto | 2310 | 4074 | |||
| 5 | Summit Yoga | 640 | 4075 | |||
| 6 | Ortiz Plumbing | 1875 | 4077 | |||
| 7 | Maple Vet | 920 | 4078 |
Show the answer
=INDEX(B2:B7,MATCH(E2,C2:C7,0)) gives 1875.
MATCH finds the invoice in C2:C7 and returns its position. INDEX returns the cell at that position in B2:B7. Both ranges start at row 2 and end at row 7.
In F2, return the name of the customer with the largest amount. Find the largest amount first, then find where it is, then return the customer from that row.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Amount | Invoice | Largest | Customer | |
| 2 | Harbor Dental | 1250 | 4071 | |||
| 3 | Pine St Bakery | 480 | 4072 | |||
| 4 | Lakeside Auto | 2310 | 4074 | |||
| 5 | Summit Yoga | 640 | 4075 | |||
| 6 | Ortiz Plumbing | 1875 | 4077 | |||
| 7 | Maple Vet | 920 | 4078 |
Show the answer
=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0)) gives Lakeside Auto.
MAX(B2:B7) is 2310. MATCH finds 2310 in the amounts and returns 3, and INDEX returns the 3rd customer. The value MATCH looks for can be a formula.
F2 holds a package size and G2 a zone. In H2, return the rate where that size's row meets that zone's column, so that changing either cell changes the answer.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Size | Zone 1 | Zone 2 | Zone 3 | Size | Zone | Rate | |
| 2 | Envelope | 6 | 8 | 11 | Large box | Zone 2 | ||
| 3 | Small box | 9 | 12 | 16 | ||||
| 4 | Medium box | 13 | 17 | 23 | ||||
| 5 | Large box | 19 | 25 | 34 | ||||
| 6 | Pallet | 85 | 110 | 140 |
Show the answer
=INDEX(B2:D6,MATCH(F2,A2:A6,0),MATCH(G2,B1:D1,0)) gives 25.
The first MATCH finds Large box in A2:A6 (position 4), the second finds Zone 2 in B1:D1 (position 2). INDEX returns row 4, column 2 of B2:D6.
Questions people ask
Why is my INDEX MATCH returning the wrong value?
Check two things. First, that the INDEX range and the MATCH range start on the same row: if one includes the header, the answer is one row off. Second, that MATCH ends with ,0: without it MATCH does an approximate match and can return the position of a different value. Both are live examples above.
Why does INDEX MATCH return #N/A?
The #N/A comes from MATCH: it didn't find the lookup value in its range. Usually the two values only look the same, or MATCH is searching the wrong column. The #N/A error page goes through the causes with a formula to fix.
Is INDEX MATCH better than VLOOKUP?
It does two things VLOOKUP can't: it returns a value from a column to the left of the one you search, and it has no column number to recount when a column is inserted. VLOOKUP is shorter to type when the lookup column is the first one. Both need the exact-match argument written out.
Should I use INDEX MATCH or XLOOKUP?
XLOOKUP does the same lookups in one function, with exact match as its default, but it exists only in Excel 2021, Excel 2024 and Microsoft 365. INDEX and MATCH also work in Excel 2016 and 2019, which matters when a workbook is shared with people on those versions.