formulastreak

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.

Orders and price list · edit M2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultM2 = 5
GHIJKLM
1SKUProductPriceStockProductCeramic pot
2PL-101Fern$9.5040Position5
3PL-104Monstera$34.0012
4PL-107Snake plant$22.0025
5PT-210Terracotta pot$12.5060
6PT-212Ceramic pot$19.0018
7TL-301Watering can$18.009
8SL-401Potting mix$7.2580

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.

Orders and price list · edit M2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultM2 = $22.00
GHIJKLM
1SKUProductPriceStockPosition3
2PL-101Fern$9.5040Price$22.00
3PL-104Monstera$34.0012
4PL-107Snake plant$22.0025
5PT-210Terracotta pot$12.5060
6PT-212Ceramic pot$19.0018
7TL-301Watering can$18.009
8SL-401Potting mix$7.2580

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.

Orders and price list · edit M2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultM2 = PL-107
GHIJKLM
1SKUProductPriceStockProductSnake plant
2PL-101Fern$9.5040SKUPL-107
3PL-104Monstera$34.0012
4PL-107Snake plant$22.0025
5PT-210Terracotta pot$12.5060
6PT-212Ceramic pot$19.0018
7TL-301Watering can$18.009
8SL-401Potting mix$7.2580

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.

Q3 sales team · edit I3 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI3 = $10,800
ABCDEFGHI
1RepRegionJulAugSepTargetRepGrace Kim
2Amara OseiNorth$12,400$13,100$11,800$36,000MonthSep
3Ben FischerSouth$9,800$10,250$12,100$30,000Sales$10,800
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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
Select M2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHIJKLM
1OrderSKUQtySKUProductPriceStockProductCeramic pot
22001PL-1042PL-101Fern$9.5040Position
32002PT-2101PL-104Monstera$34.0012
42003TL-3013PL-107Snake plant$22.0025
52004PL-1011PT-210Terracotta pot$12.5060
62005SL-4014PT-212Ceramic pot$19.0018
72006PL-1041TL-301Watering can$18.009
82007PT-2122SL-401Potting mix$7.2580
92008PL-1071

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.

Practise it now