INDEX & MATCH practice exercises
MATCH finds the position of a value in a column. INDEX returns the item at a given position. Together they make a lookup that can go in any direction and survives inserted columns, which is why many spreadsheet pros still prefer it.
Syntax
INDEX(return_range, MATCH(search_key, lookup_range, 0))
MATCH(search_key, lookup_range, 0)- Returns the row number of search_key inside lookup_range. The
0means exact match. INDEX(return_range, n)- Returns the n-th item of return_range.
The practice data
A product list with SKU, name, price and stock. F2 holds a SKU to look up. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | SKU to find | |
| 2 | 101 | Widget | 5 | 40 | 103 | |
| 3 | 102 | Gadget | 20 | 15 | ||
| 4 | 103 | Gizmo | 50 | 0 | ||
| 5 | 104 | Doohickey | 12 | 8 | ||
| 6 | 105 | Sprocket | 3 | 120 |
Worked example
=INDEX(C2:C6,MATCH("Gizmo",B2:B6,0))
MATCH finds "Gizmo" at position 3 of B2:B6; INDEX returns the 3rd price. Result:
| 50 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the stock of "Widget".
2 Return the product name for SKU 104.
3 Return the name of the most expensive product.
Master INDEX & MATCH in the full course
Lesson 10 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon INDEX & MATCH mistakes
- Leaving out the
0in MATCH. The default is an approximate match on sorted data. - Using ranges that start on different rows, so the position from MATCH points at the wrong item in INDEX.
- Mixing a full-column range like
B:Bwith a partial one likeC2:C6.
FAQ
Is INDEX MATCH better than VLOOKUP?
It is more flexible: it can look left and doesn't break when columns are inserted. XLOOKUP offers the same benefits with a simpler syntax.
Can INDEX MATCH use two criteria?
Yes, for example MATCH(1,(A2:A9="Ana")*(B2:B9="West"),0) inside an ARRAYFORMULA, or use FILTER instead.