Excel INDEX MATCH

MATCH finds the position of a value in a list. INDEX returns the item at a given position in a range. Together, =INDEX(return_range, MATCH(what, lookup_range, 0)) looks a value up in one column and returns the matching value from another, left or right, in every version of Excel.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

INDEX: array: The range to take a value from, for example D2:D9.
INDEX: row_num: The position of the row inside that range. In D2:D9, 1 is D2, 2 is D3, and so on.
INDEX: column_num: Optional for a one-column range. For a table, the position of the column inside the range.
MATCH: lookup_value: The value to find, usually a cell such as F2.
MATCH: lookup_array: The single column (or row) to search.
MATCH: match_type: 0 for an exact match, which is what you want almost every time. 1 (the default if you leave it out) finds the largest value less than or equal to the one you asked for and needs ascending data. -1 finds the smallest value greater than or equal to it and needs descending data.

How INDEX and MATCH work

Each function does half of a lookup. MATCH answers "which position is it at?". INDEX answers "what is at that position?". Put one inside the other and you have a full lookup.

MATCH: find the position

In the sheet below, customer names are in B2:B9. To find where Sam is in that list:

=MATCH("Sam", B2:B9, 0)

Sam is the 7th name, so MATCH returns 7. The position counts from the first cell of the range you give it, not from the top of the sheet. The 0 asks for an exact match. Without it MATCH does an approximate match, which gives wrong answers on unsorted lists.

INDEX: get the item at a position

Amounts are in D2:D9. The item at position 7 is:

=INDEX(D2:D9, 7)

That returns 70, the 7th amount.

Combining them

Replace the 7 with the MATCH formula and you get the amount for Sam, whatever position Sam is at:

=INDEX(D2:D9, MATCH("Sam", B2:B9, 0))

MATCH searches B2:B9 for Sam and returns 7.
INDEX takes the 7th item of D2:D9.
The result is 70.

The two ranges must cover the same rows. If one starts at row 2 and the other at row 1, every answer is off by one.

Looking left

The return range is separate from the lookup range, so it can be on either side. To find the order number (column A) of a given item (column C), search column C and return column A. VLOOKUP cannot do this.

Two-way lookups

INDEX also accepts a column number, so one INDEX can take a cell from a grid. Use one MATCH for the row and another for the column:

=INDEX(B12:D15, MATCH("East", A12:A15, 0), MATCH("Feb", B11:D11, 0))

INDEX MATCH or XLOOKUP?

If everyone who opens the file has Excel 2021 or later, or Microsoft 365, XLOOKUP is shorter and easier to read. INDEX MATCH works in every Excel version, and many people still find it the clearest way to write a two-way lookup.

Practise INDEX MATCH

Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.

	A	B	C	D	F	G	H
1	Order	Customer	Item	Amount	Lookup	Result
2	1001	Rosa	Dock	180	Sam
3	1002	Omar	Monitor	210	3
4	1003	Tara	Headset	60	Laptop
5	1004	Nina	Laptop	950
6	1005	Uri	Cable	12
7	1006	Petra	Keyboard	45	Customer	Amount
8	1007	Sam	Webcam	70	Omar
9	1008	Quinn	Mouse	25	Quinn
10					Rosa
11	Region	Jan	Feb	Mar	Uri
12	North	120	135	150	Region	Month	Sales
13	South	90	95	110	East	Feb
14	East	200	180	210
15	West	75	85	60
16
17

0 of 5 done

1
In G2, find the position of the customer named in F2 in the list B2:B9. Use MATCH with an exact match.

Show answer
=MATCH(F2,B2:B9,0)
2
F3 holds a position. In G3, use INDEX to return the amount at that position in D2:D9.

Show answer
=INDEX(D2:D9,F3)
3
Combine both. Write one formula in G8 that finds the amount for the customer in F8. It is filled down to G11 for you, and all four rows must be right.

Show answer
=INDEX($D$2:$D$9,MATCH(F8,$B$2:$B$9,0))
4
Find the order number of the item named in F4. The order numbers in column A are to the left of the items in column C. Write it in G4.

Show answer
=INDEX(A2:A9,MATCH(F4,C2:C9,0))
5
The grid in A11:D15 shows sales by region and month. In H13, find the sales for the region in F13 and the month in G13 with one INDEX and two MATCH functions.

Show answer
=INDEX(B12:D15,MATCH(F13,A12:A15,0),MATCH(G13,B11:D11,0))

Common INDEX MATCH problems

#N/A

MATCH did not find the value. Check for typing differences and extra spaces (TRIM removes them), for numbers stored as text on one side, and that you are searching the right column. Wrap the formula in IFERROR or IFNA if a miss is normal.

#REF!

The position is larger than the range, or you gave INDEX a row or column number that does not exist. A common cause is a MATCH range that is longer than the INDEX range.

Result is one row off

The INDEX range and the MATCH range start in different rows. If you search B2:B9, return from D2:D9, not D1:D9.

Wrong value with no error

The third argument of MATCH is missing or not 0, so Excel did an approximate match on unsorted data. Add 0.

Works in the first row, breaks when filled down

A range moved as the formula was copied. Lock the ranges with dollar signs, for example $D$2:$D$9, and leave the lookup cell, such as F8, unlocked so it moves down.

Questions

Why use INDEX MATCH instead of VLOOKUP?

INDEX MATCH can return a column to the left of the lookup column, it does not depend on a column number so inserting columns does not break it, and it only reads the two columns it needs. VLOOKUP is shorter for simple lookups on the right.

Is INDEX MATCH better than XLOOKUP?

XLOOKUP is shorter and easier to read, but it needs Excel 2021 or later, or Microsoft 365. INDEX MATCH works in every version of Excel, so use it when the file is shared with people on older versions.

What does the 0 in MATCH mean?

It is the match type and means an exact match. Leaving it out means 1, which finds the largest value less than or equal to the one you asked for and assumes the list is sorted in ascending order.

How do I do a two-way lookup in Excel?

Use one INDEX over the whole grid with two MATCH functions: =INDEX(grid, MATCH(row_value, row_labels, 0), MATCH(column_value, column_labels, 0)). The first MATCH picks the row and the second picks the column.

Can INDEX MATCH return more than one match?

No. MATCH returns the position of the first match only. To return every match, use FILTER in Excel 365.

Is MATCH case-sensitive?

No. "sam" and "Sam" match each other.

Excel INDEX MATCH

How INDEX and MATCH work

MATCH: find the position

INDEX: get the item at a position

Combining them

Looking left

Two-way lookups

INDEX MATCH or XLOOKUP?

Practise INDEX MATCH

Common INDEX MATCH problems

#N/A

#REF!

Result is one row off

Wrong value with no error

Works in the first row, breaks when filled down

Questions

Why use INDEX MATCH instead of VLOOKUP?

Is INDEX MATCH better than XLOOKUP?

What does the 0 in MATCH mean?

How do I do a two-way lookup in Excel?

Can INDEX MATCH return more than one match?

Is MATCH case-sensitive?

Related lessons