INDEX and MATCH in Excel
INDEX and MATCH work as a pair to look things up in any direction, in every version of Excel.
What INDEX / MATCH does
MATCH finds the position of a value in a range, for example that Backpack is the third item. INDEX then returns the item at that position from another range. Together they do what VLOOKUP does, without needing the lookup value to be on the left.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
- return_range
- The cells that hold the answer you want.
- lookup_value
- What to find.
- lookup_range
- The cells to search for it. It must be the same size as the return range.
- 0
- Asks MATCH for an exact match.
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Look up product | Price | |
| 2 | 1001 | Notebook | 8 | Backpack | 40 | |
| 3 | 1004 | Desk lamp | 25 | |||
| 4 | 1007 | Backpack | 40 | |||
| 5 | 1010 | Headphones | 60 |
MATCH finds Backpack in position 3 of B2:B5. INDEX returns the third item of C2:C5, which is 40.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Look up values from tables where the key is not in the first column.
Sales & Ops
Pull prices or stock levels by name.
Data cleanup
Build lookups that survive inserted columns.
Common mistakes
- Leaving out the 0 in MATCH. Without it MATCH looks for the nearest match, which needs sorted data.
- Return and lookup ranges that differ in size or start on different rows, so the positions do not line up.
- Swapping the arguments. INDEX takes the answer range first. MATCH takes the value to find first.
The INDEX / MATCH course
- 1Look up with INDEX and MATCHFree practice belowPractise
- 2Look left of the keyComing soon
- 3Two-way lookupsComing soon
- 4Approximate matchesComing soon
- 5Dynamic rangesComing soon
Common questions
Why use INDEX and MATCH when XLOOKUP exists?
They work in every version of Excel, and you will meet them in many existing workbooks.
Does it break when I insert a column?
Less often than VLOOKUP, because it points at the columns directly instead of counting columns.