INDEX and MATCH in Excel: how to use it
MATCH tells you the position of something, and INDEX fetches what's at that position. Together they look up in any direction, in every version of Excel.
Try the first exercises for freeIn the lesson INDEX with MATCH
What do INDEX and MATCH do?
MATCH looks up a value in a column and tells you which position it's in: the first, the second, the third. INDEX does the opposite: you give it a position, and it fetches what's there.
Together they look something up, like VLOOKUP, but more flexibly: the column with the answer can be anywhere, even to the left, and an extra column in your table doesn't confuse the formula. They work in every version of Excel.
When do you use INDEX and MATCH?
- The price for a product name: =INDEX(C2:C50,MATCH(E2,B2:B50,0)).
- Looking to the left, such as the code for a name: =INDEX(A2:A50,MATCH(E2,B2:B50,0)).
- The name with the highest revenue: =INDEX(A2:A50,MATCH(MAX(C2:C50),C2:C50,0)).
- A value from a table with rows and columns, such as product and month: with MATCH for the row and for the column.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
What's in a given position in a column.
Filled in: =INDEX(C2:C5,3)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
INDEX | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | INDEX |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array | With INDEX: the column you take the answer from. | C2:C5 |
row_num | With INDEX: which position the answer is in. That number comes from MATCH. | 3 |
, | A comma separates the arguments from each other. | , |
INDEX and MATCH step by step
E1 contains =INDEX(C2:C5,MATCH("Monitor",B2:B5,0)). MATCH gives 3, and INDEX reads the third price. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Start with the inner part. In E1, type MATCH with what you're looking for and where: "Monitor" in the product names B2:B5, and 0 for an exact match.
=MATCH("Monitor",B2:B5,0) - Feel free to press Enter for a moment: you get 3, because Monitor is in the third position. Then click back into the formula.
- Put INDEX in front: after the equals sign, INDEX with a bracket, the column with the answer, C2:C5, and a comma.
=INDEX(C2:C5,MATCH("Monitor",B2:B5,0) - Close the last bracket and press Enter: INDEX reads the third price, 219.
=INDEX(C2:C5,MATCH("Monitor",B2:B5,0))
More examples
Looking to the left
E1 contains =INDEX(A2:A5,MATCH("Desk",B2:B5,0)). The code is to the left of the product name. VLOOKUP can't do that, but INDEX and MATCH can.
The name with the highest revenue
E1 contains =INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0)). MAX finds 1200, MATCH says that's in position 1, and INDEX gives the region in that position: Leeds.
Row and column at once
D6 contains =INDEX(B2:D4,MATCH(B6,A2:A4,0),MATCH(B7,B1:D1,0)). The first MATCH finds the row for Monitor, the second the column for Feb. INDEX fetches what's at the crossing point: 28.
Why still use it?
It works in old versions of Excel too, and an extra column in your table doesn't confuse it. It also comes up regularly in job application tests for finance roles.
Looking to the left
The result column may be to the left of the lookup column. If you're looking for the code for a product name and the code is at the front, you write =INDEX(A2:A5,MATCH(E2,B2:B5,0)). VLOOKUP can't do that.
Looking up by row and column at once
If you have a table with products in the rows and months in the columns, use MATCH twice: =INDEX(B2:E5,MATCH(G1,A2:A5,0),MATCH(G2,B1:E1,0)). The first finds the row, the second the column.
Where it often goes wrong
- Don't forget the 0 in MATCH, otherwise you're not matching exactly.
Practise this in the lesson INDEX with MATCH. The first exercises are free.
Frequently asked questions
Is XLOOKUP better?
It's simpler, but doesn't work in older versions. So know both.