INDEX and MATCH in Excel
Last updated
INDEX + MATCH is a lookup built from two functions: MATCH finds the position of a value in a column, and INDEX returns the value at that position in another column. For example, =INDEX(C2:C9,MATCH(E2,B2:B9,0)) finds the store in E2 and returns its sales from column C. The 0 asks for an exact match.
INDEX + MATCH syntax
=INDEX(array, MATCH(lookup_value, lookup_array, 0))
- array
- The range that holds the result, e.g.
C2:C9. It can be on either side of the lookup column. - lookup_value
- The value to find, such as a store name or a code.
- lookup_array
- The column to search, e.g.
B2:B9. It must start and end on the same rows as array, so the positions line up. - 0 (match_type)
0for an exact match. If you leave it out, MATCH uses approximate match, which needs a sorted column and can return a wrong row without any error.
Practice INDEX + MATCH here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find a store’s position in the list
In F2, write a MATCH that returns the position of the store in E2 within the list B2:B9.
| Store ID | Store | Sales ($) | Store | Position | |
| 208 | Denver | 48,200.00 | Chicago | ||
| 415 | Boston | 61,750.00 | |||
| 133 | Seattle | 39,400.00 | |||
| 362 | Austin | 72,300.00 | |||
| 527 | Chicago | 55,900.00 | |||
| 291 | Miami | 44,100.00 | |||
| 446 | Portland | 67,800.00 | |||
| 174 | Atlanta | 52,600.00 |
How to write INDEX + MATCH, step by step
- Click the cell where you want the result and type
=INDEX(. - Select the column that holds the result (for example the sales), then type a comma.
- Type
MATCH(, click the cell with the value to find, type a comma and select the column to search, on the same rows. - Type
,0))to ask for an exact match and close both functions, then press Enter.
INDEX + MATCH examples
Sales from a store name
=INDEX($C$2:$C$9,MATCH(E2,$B$2:$B$9,0))
Returns the sales of the store in E2. Lock both ranges with $ before copying the formula down.
Look up to the left
=INDEX($A$2:$A$9,MATCH(E2,$B$2:$B$9,0))
Returns the store ID from column A, to the left of the names. VLOOKUP can’t do this.
Two-way lookup
=INDEX($B$2:$E$7,MATCH(G2,$A$2:$A$7,0),MATCH(H2,$B$1:$E$1,0))
One MATCH finds the row, the other finds the column, and INDEX returns the cell where they cross.
Show a message instead of #N/A
=IFERROR(INDEX($C$2:$C$9,MATCH(E2,$B$2:$B$9,0)),"Not found")
IFERROR replaces the #N/A of a missing value with your own text.
Common INDEX + MATCH errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | MATCH can’t find the value: a typo, an extra space, or a number stored as text. Or the ranges moved when you copied the formula. | Check the value, clean the data with TRIM, and lock the ranges with $. |
| #REF! | The position is larger than the number of rows (or columns) in the INDEX range. | Make the INDEX range the same size as the MATCH range. |
| Wrong value, no error | The 0 is missing, so MATCH uses approximate match on an unsorted column. | End MATCH with 0: MATCH(E2,B2:B9,0). |
| Value from the wrong row | The two ranges don’t start on the same row, e.g. C1:C9 with B2:B9, so every position is shifted. | Select both ranges from the same first row to the same last row. |
INDEX + MATCH vs VLOOKUP and XLOOKUP
VLOOKUP needs the lookup value in the first column and returns a column by its number, so it can’t look left and breaks when you insert a column. INDEX + MATCH has neither limit and works in every Excel version. XLOOKUP (Excel 2021 and Microsoft 365) does the same job in one function, but files shared with older versions still rely on INDEX + MATCH, so it’s worth knowing.
Frequently asked questions
Why use INDEX and MATCH instead of VLOOKUP?
INDEX + MATCH can return a column to the left of the lookup column, keeps working when you insert or delete columns, and can look up a row and a column at once.
What does the 0 mean in MATCH?
It asks for an exact match. Without it, MATCH assumes the column is sorted and may return the position of a different value, with no error.
Why does my INDEX MATCH return #N/A?
MATCH didn’t find the value. Look for typos, extra spaces and numbers stored as text, and check that the ranges didn’t move when you copied the formula.
How do I do a two-way lookup?
Give INDEX a row and a column, each found by MATCH: =INDEX(B2:E7,MATCH(G2,A2:A7,0),MATCH(H2,B1:E1,0)).
Keep practising
All 5 INDEX + MATCH exercises, with explanations and progress tracking.
Open the INDEX + MATCH practice set