Confident Cells

INDEX and MATCH: a simple worked example

20 November 2026 · 2 min read

INDEX and MATCH together look up a value: MATCH finds which row it's on, and INDEX brings back the value from that row of another column. They work in every version of Excel and can look left, which VLOOKUP can't.

Two small jobs

It's easiest to see each function on its own first. Here's a price list.

ABC
1ProductPriceCode
2Stapler£6.50P-204
3Desk lamp£18.00P-117
4Box files£4.20P-310
5Whiteboard£35.00P-152

MATCH: which position is it in?

Where is code P-310 in the Code column? Counting down: P-204 is 1st, P-117 is 2nd, P-310 is 3rd.

=MATCH("P-310",C2:C5,0)

This gives 3. The 0 at the end means exact match.

INDEX: what's in that position?

What's 3rd in the Price column? Counting down: £6.50, £18.00, £4.20.

=INDEX(B2:B5,3)

This gives £4.20.

Putting them together

Put the MATCH where the 3 was, and point to a cell instead of typing the code:

=INDEX(B2:B5,MATCH(F1,C2:C5,0))

Read it from the inside out: find the code in F1 in the Code column (MATCH says it's 3rd), then take the 3rd price (INDEX gives £4.20).

ABCDEF
1ProductPriceCodeCodeP-310
2Stapler£6.50P-204Price£4.20
3Desk lamp£18.00P-117
4Box files£4.20P-310
5Whiteboard£35.00P-152
F2 holds =INDEX(B2:B5,MATCH(F1,C2:C5,0)).

The code is to the right of the price, so VLOOKUP couldn't do this. In Excel 365, XLOOKUP does it in one function, but INDEX and MATCH work everywhere.

Common mistakes

Leaving off the 0 in MATCH

Without it, MATCH does an approximate match, which expects the list to be sorted and can quietly give the wrong position. Always end with ,0 for an exact match.

The two ranges don't line up

INDEX's range and MATCH's range must start on the same row. INDEX(B2:B5,…) with MATCH(F1,C3:C6,0) is out by one, so every answer comes from the row below.

#N/A: MATCH can't find it

The value isn't in the column, or it has a stray space or is a number stored as text. Wrap the whole formula in IFERROR to show a message instead.

#REF!: the position is bigger than the INDEX range

This happens when the two ranges are different lengths. Make them the same size.

Try it now

Your turn

In F2, find the price of the product whose code is in F1, using INDEX and MATCH. The codes are to the right of the prices.

ABCDEF
1ProductPriceCodeCodeP-268
2Stapler£6.50P-204Price
3Desk lamp£18.00P-117
4Box files£4.20P-310
5Whiteboard£35.00P-152
6Hole punch£7.75P-268
7Paper (5 reams)£21.99P-131
8Marker pens£5.40P-225

Where it works

INDEX and MATCH work in every version of Excel, in Google Sheets and in LibreOffice Calc.