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 0 means 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.

ABCDEF
1SKUProductPriceStockSKU to find
2101Widget540103
3102Gadget2015
4103Gizmo500
5104Doohickey128
6105Sprocket3120

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".

H2

2 Return the product name for SKU 104.

H2

3 Return the name of the most expensive product.

H2

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 pricing

Common INDEX & MATCH mistakes

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.

Keep practicing