INDEX MATCH, explained
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=INDEX(return_range, MATCH(key, lookup_range, 0)). MATCH finds the key's position, INDEX returns the value at that position from another column, on either side. Add a second MATCH for the column to do two-way lookups.
INDEX/MATCH is the lookup that works in every version of Excel and in Google Sheets, in any direction. It's two simple functions combined, so learn each one first.
MATCH finds a position
MATCH(key, range, 0) returns where the key is in a single row or column: 1 for the first cell, 2 for the second, and so on. The final 0 means exact match. Edit M1 to another product and watch the position change.
| G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | Product | Ceramic pot | |
| 2 | PL-101 | Fern | $9.50 | 40 | Position | 5 | |
| 3 | PL-104 | Monstera | $34.00 | 12 | |||
| 4 | PL-107 | Snake plant | $22.00 | 25 | |||
| 5 | PT-210 | Terracotta pot | $12.50 | 60 | |||
| 6 | PT-212 | Ceramic pot | $19.00 | 18 | |||
| 7 | TL-301 | Watering can | $18.00 | 9 | |||
| 8 | SL-401 | Potting mix | $7.25 | 80 |
INDEX returns the nth value
INDEX(range, n) gives you the nth value of a range. On its own it's not exciting; it becomes useful when MATCH supplies the n.
| G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | Position | 3 | |
| 2 | PL-101 | Fern | $9.50 | 40 | Price | $22.00 | |
| 3 | PL-104 | Monstera | $34.00 | 12 | |||
| 4 | PL-107 | Snake plant | $22.00 | 25 | |||
| 5 | PT-210 | Terracotta pot | $12.50 | 60 | |||
| 6 | PT-212 | Ceramic pot | $19.00 | 18 | |||
| 7 | TL-301 | Watering can | $18.00 | 9 | |||
| 8 | SL-401 | Potting mix | $7.25 | 80 |
Together: a lookup in any direction
INDEX(return_range, MATCH(key, lookup_range, 0)): MATCH finds the row, INDEX returns the value from that row in another column. The return column can be on either side, which is how you look up to the left.
| G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | Product | Snake plant | |
| 2 | PL-101 | Fern | $9.50 | 40 | SKU | PL-107 | |
| 3 | PL-104 | Monstera | $34.00 | 12 | |||
| 4 | PL-107 | Snake plant | $22.00 | 25 | |||
| 5 | PT-210 | Terracotta pot | $12.50 | 60 | |||
| 6 | PT-212 | Ceramic pot | $19.00 | 18 | |||
| 7 | TL-301 | Watering can | $18.00 | 9 | |||
| 8 | SL-401 | Potting mix | $7.25 | 80 |
Two-way lookups
Give INDEX a row and a column number, each from its own MATCH, and you can pick any cell from a grid by its row and column labels.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Rep | Grace Kim | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Month | Sep | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Sales | $10,800 | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | |||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | |||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | |||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | |||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | |||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Check yourself
One real exercise from the “Lookups” unit. Checked on our server; no account needed.In which position of the price list is the product in M1? Use MATCH on the Product column and put the answer in M2.
- Use MATCH
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | SKU | Product | Price | Stock | Product | Ceramic pot | ||||
| 2 | 2001 | PL-104 | 2 | PL-101 | Fern | $9.50 | 40 | Position | |||||
| 3 | 2002 | PT-210 | 1 | PL-104 | Monstera | $34.00 | 12 | ||||||
| 4 | 2003 | TL-301 | 3 | PL-107 | Snake plant | $22.00 | 25 | ||||||
| 5 | 2004 | PL-101 | 1 | PT-210 | Terracotta pot | $12.50 | 60 | ||||||
| 6 | 2005 | SL-401 | 4 | PT-212 | Ceramic pot | $19.00 | 18 | ||||||
| 7 | 2006 | PL-104 | 1 | TL-301 | Watering can | $18.00 | 9 | ||||||
| 8 | 2007 | PT-212 | 2 | SL-401 | Potting mix | $7.25 | 80 | ||||||
| 9 | 2008 | PL-107 | 1 |
Common mistakes
Leaving out MATCH's last argument. The default (1) assumes sorted data and finds the nearest smaller value instead of an exact match.
Using ranges of different heights in INDEX and MATCH, so the position points at the wrong row.
Forgetting
$when filling an INDEX/MATCH down a column.
Questions
Is INDEX MATCH better than VLOOKUP?+
It's more flexible: it can look left, it doesn't break when you insert columns, and it works in every Excel version. VLOOKUP is shorter for simple lookups to the right.
Should I use XLOOKUP instead of INDEX MATCH?+
If everyone opening the file has Excel 2021, Microsoft 365 or Google Sheets, XLOOKUP is simpler. INDEX/MATCH is the safe choice for older versions.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.