INDEX and MATCH explained, with practice exercises

In short: =INDEX(return_column,MATCH(lookup_value,lookup_column,0)). MATCH returns the row where the lookup value appears, INDEX returns the value from that row of the return column. The 0 means exact match.

Try it now

The sheet lists employees with their ID, name and department. Find the ID for the name in E2. The IDs are to the left of the names, which is exactly where VLOOKUP gives up. Click the yellow cell and write the formula.

Task In F2, find the employee ID of the person whose name is in E2. The ID sits to the left of the name, so use INDEX and MATCH.

	A	B	C	D	E	F
1	Employee ID	Name	Department		Name	Employee ID
2	1012	Murat Kaya	Sales		Sara Klein
3	1007	Jana Weber	Purchasing
4	1023	Lena Hoffmann	Finance
5	1044	Sara Klein	Sales
6	1031	Tom Schulz	HR
7	1019	Ali Demir	Purchasing

The INDEX MATCH formula

Argument	Meaning	In the example
array (INDEX)	The column the result comes from	A2:A7 (IDs)
lookup_value (MATCH)	The value you are looking for	E2 (the name)
lookup_array (MATCH)	The column to search	B2:B7 (names)
match_type (MATCH)	0 for an exact match	0

Excel works from the inside out. MATCH(E2,B2:B7,0) finds Sara Klein as the fourth entry of B2:B7 and returns 4. That leaves =INDEX(A2:A7,4), which returns the fourth value of A2:A7: 1044.

INDEX on its own

INDEX does not search. You hand it a position and it hands back whatever is there. The syntax is =INDEX(array,row_num,[column_num]), so =INDEX(A2:C7,4,3) returns the cell in the fourth row and third column of A2:C7, which is C5. Positions count from the top left corner of the range, not from row 1 of the sheet. For a single column range you can leave out the column number.

MATCH on its own

MATCH is the reverse: you give it a value and it tells you the position. The syntax is =MATCH(lookup_value,lookup_array,[match_type]), and the match type controls how it searches:

match_type	Finds	Requires
0	exactly the lookup value	nothing, any order works
1 or omitted	the largest value less than or equal to the lookup value	data sorted ascending
-1	the smallest value greater than or equal to the lookup value	data sorted descending

For names, codes and IDs you want 0 almost every time. Type 1 suits tiered tables such as discount levels that start at a certain quantity.

One more: rank in a sales list

This list is sorted by revenue, best first. MATCH alone is enough to tell you someone's rank.

Task The list is sorted by revenue with the top seller first. In F2, use MATCH to find the rank of the person in E2.

	A	B	C	D	E	F
1	Name	Revenue $			Name	Rank
2	Sara Klein	48200			Lena Hoffmann
3	Murat Kaya	45900
4	Lena Hoffmann	41300
5	Tom Schulz	38750
6	Jana Weber	36100
7	Ali Demir	29400

Why INDEX MATCH beats VLOOKUP

It looks left

VLOOKUP always searches the first column of its table and can only return values to the right of it. With INDEX and MATCH the lookup column and the return column are separate ranges, so their order does not matter.

Inserted columns do not break it

=VLOOKUP(E2,A2:D7,4,FALSE) hard codes the 4. Insert a column inside the table and the formula quietly returns data from the wrong column. INDEX and MATCH only use references, and Excel adjusts those when columns are added.

Two-way lookup with two MATCH functions

Say regions are in A2:A6, months in B1:M1 and revenue in B2:M6. One MATCH picks the row, a second one picks the column, and INDEX returns the value where they cross:

=INDEX(B2:M6,MATCH(O2,A2:A6,0),MATCH(P2,B1:M1,0))

With "South" in O2 and "March" in P2 you get March revenue for the South region. The row range must be as tall as the array and the column range as wide.

Common INDEX MATCH errors

Leaving out the 0

Without the third argument MATCH uses type 1, an approximate match meant for sorted data. On an unsorted list it often returns a wrong position with no error at all. Always type the 0.

Ranges of different size

Both ranges need to start in the same row and have the same length. =INDEX(A1:A7,MATCH(E2,B2:B7,0)) is always one row off, because INDEX counts from row 1 while MATCH counts from row 2. If the lookup range is longer than the INDEX array, you can also get #REF!.

#N/A

MATCH could not find the lookup value. Typos, trailing spaces and numbers stored as text are the usual suspects. If a missing value is expected, wrap the formula in IFERROR: =IFERROR(INDEX(A2:A7,MATCH(E2,B2:B7,0)),"not found").

INDEX and MATCH in German Excel

INDEX keeps its name in German Excel, MATCH becomes VERGLEICH, and arguments are separated by semicolons:

English	German
=INDEX(A2:A7,MATCH(E2,B2:B7,0))	=INDEX(A2:A7;VERGLEICH(E2;B2:B7;0))
XLOOKUP	XVERWEIS
XMATCH	XVERGLEICH
#N/A	#NV

Our exercises accept both spellings. Pick your Excel language below each exercise.

Which Excel versions have INDEX and MATCH?

All of them, including Excel 2016 and 2019, Excel for Mac and Excel for the web. That makes the pair the safe choice for workbooks that also have to run on older installations.

Microsoft 365, Excel 2021 and Excel 2024 also offer XLOOKUP. =XLOOKUP(E2,B2:B7,A2:A7) gives the same result as the first exercise, matches exactly by default and has its own argument for values that are not found. Open the file in Excel 2019 or earlier, though, and XLOOKUP shows #NAME?.

FAQ