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.

Skip to the 3 exercises ↓

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 array to 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 when array is 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 write 0.
Live exampleCustomer on the invoice in E2
F2fx
ABCDEF
1CustomerAmountInvoiceFind invoiceCustomer
2Harbor Dental125040714075Summit Yoga
3Pine St Bakery4804072
4Lakeside Auto23104074
5Summit Yoga6404075
6Ortiz Plumbing18754077
7Maple Vet9204078

How Excel reads it

  1. 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.
  2. 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:

Live exampleWrong row: INDEX starts at the header
F2fx
ABCDEF
1CustomerAmountInvoiceFind invoiceCustomer
2Harbor Dental125040714075Lakeside Auto
3Pine St Bakery4804072
4Lakeside Auto23104074
5Summit Yoga6404075
6Ortiz Plumbing18754077
7Maple Vet9204078

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:

Live exampleNo 0: a customer for an invoice that doesn't exist
F2fx
ABCDEF
1CustomerAmountInvoiceFind invoiceCustomer
2Harbor Dental125040714076Summit Yoga
3Pine St Bakery4804072
4Lakeside Auto23104074
5Summit Yoga6404075
6Ortiz Plumbing18754077
7Maple Vet9204078

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:

Live exampleRate for a size and a zone
H2fx
ABCDEFGH
1SizeZone 1Zone 2Zone 3SizeZoneRate
2Envelope6811Medium boxZone 323
3Small box91216
4Medium box131723
5Large box192534
6Pallet85110140

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.

Exercise 1Amount of one invoice

Someone types an invoice number in E2. In F2, return the amount of that invoice with INDEX and MATCH.

F2fx
ABCDEF
1CustomerAmountInvoiceFind invoiceAmount
2Harbor Dental125040714077
3Pine St Bakery4804072
4Lakeside Auto23104074
5Summit Yoga6404075
6Ortiz Plumbing18754077
7Maple Vet9204078
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.

Exercise 2Who has the largest invoice

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.

F2fx
ABCDEF
1CustomerAmountInvoiceLargestCustomer
2Harbor Dental12504071
3Pine St Bakery4804072
4Lakeside Auto23104074
5Summit Yoga6404075
6Ortiz Plumbing18754077
7Maple Vet9204078
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.

Exercise 3A two-way lookup

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.

H2fx
ABCDEFGH
1SizeZone 1Zone 2Zone 3SizeZoneRate
2Envelope6811Large boxZone 2
3Small box91216
4Medium box131723
5Large box192534
6Pallet85110140
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.

Keep going

Lesson 6 is seven drills on a team list: MATCH alone, INDEX alone, the two together, a lookup to the left, a two-way lookup, a missing name, and locking the ranges with $ before copying a formula down. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

VLOOKUP function, with live examplesXLOOKUP function, with live examplesIFERROR and IFNA, with live examples#N/A error in Excel: meaning and fixes#REF! error in Excel: meaning and fixesINDEX MATCH practice exercises (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: INDEX function, Microsoft Support: MATCH function, Microsoft Support: Look up values with VLOOKUP, INDEX, or MATCH. Last checked November 2026.