Excel practice · Lookups

INDEX & MATCH practice exercises

10 graded problems with realistic data, all medium.

Two functions, paired together: MATCH finds the position, INDEX returns the value at that position. The 10 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.

New to the function? Read the INDEX & MATCH guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Pull the 3rd item from a list
  2. Pull one cell out of a grid
  3. Find the 4th name on a roster
  4. Pull one month's spend from a row
  5. Pull the 5th student's score
  6. Find where "Clips" sits in the list
  7. Find an ID's position in a list
  8. See what MATCH returns when nothing matches
  9. Find where the top score sits
  10. Find a month's position in the year

The exercises

1. Pull the 3rd item from a list Medium

Return the price of whichever item sits in position 3 of the list.

ABCDE
1ItemPriceRow #:3
2Pens1.2Price:?
3Pads3.5
4Clips0.8
5Tape2.1

Enter a formula in E2.

Show a hint

INDEX(range, position) returns whatever is at that position in the range: =INDEX(B2:B5,E1).

INDEX by itself is simple. The payoff comes once you pair it with MATCH to find the position automatically.

2. Pull one cell out of a grid Medium

A small table has 2 rows and 3 quarter columns. Return the value at row 2, column 3.

ABCDE
1Q1Q2Q3Row:2
2102030Col:3
3405060Value:?

Enter a formula in E3.

Show a hint

With a 2D range, INDEX takes both a row and a column position: =INDEX(A2:C3,E1,E2).

This is INDEX's full form (row AND column) for pulling one cell out of an actual table, not just a single column.

3. Find the 4th name on a roster Medium

Return whichever name sits in position 4 of the roster.

ABCDE
1NamePosition:4
2OwenName:?
3Priya
4Lucas
5Maya
6Farah

Enter a formula in E2.

Show a hint

=INDEX(A2:A6,E1).

INDEX doesn't care whether the range holds numbers or text: position is position.

4. Pull one month's spend from a row Medium

Four months of spend are laid out across a single row. Return whichever month sits in the given column position.

ABCD
1JanFebMarApr
2400350500420
3
4Column #:3
5Spend:?

Enter a formula in B5.

Show a hint

When the range is a single row, the one position INDEX needs is the column: =INDEX(A2:D2,B4).

A single-row (or single-column) range is the one case where INDEX only needs one position instead of two.

5. Pull the 5th student's score Medium

Return whichever score sits in position 5 of the list.

ABCDE
1StudentScorePosition:5
2Lea88Score:?
3Noah76
4Ines95
5Marco60
6Priya91

Enter a formula in E2.

Show a hint

=INDEX(B2:B6,E1).

Once this feels automatic, INDEX plus a MATCH-found position (next section) replaces VLOOKUP entirely.

6. Find where "Clips" sits in the list Medium

Return the position of "Clips" within the item list, not the value, just where it is.

ABCDE
1ItemFind:Clips
2PensPosition:?
3Pads
4Clips
5Tape

Enter a formula in E2.

Show a hint

MATCH(lookup_value, range, 0), where the 0 means exact match, which is almost always what you want: =MATCH(E1,A2:A5,0).

MATCH answers a different question than VLOOKUP (not "what's there" but "where is it"), which is exactly what INDEX needs as its position argument.

7. Find an ID's position in a list Medium

Return the position of employee ID 1077 within the ID list.

ABCDE
1IDFind ID:1077
21021Position:?
31042
41058
51077
61089

Enter a formula in E2.

Show a hint

=MATCH(E1,A2:A6,0).

Numeric MATCH works identically to text MATCH: position is position, regardless of data type.

8. See what MATCH returns when nothing matches Medium

"Rome" isn't in this city list. See what MATCH actually returns when there's no match.

ABCDE
1CityFind:Rome
2ParisPosition:?
3Lyon
4Nice

Enter a formula in E2.

Show a hint

Type =MATCH(E1,A2:A4,0) exactly like that and see what comes back.

MATCH (like VLOOKUP) returns a real #N/A error on no match. This is exactly the case IFERROR exists to catch.

9. Find where the top score sits Medium

Return the position of the score 70 within the score list.

ABCDE
1ScoreFind:70
288Position:?
376
495
560
692
770

Enter a formula in E2.

Show a hint

=MATCH(E1,A2:A7,0).

Finding a value's rank or position is the first step in a lot of ranking and lookup formulas.

10. Find a month's position in the year Medium

Return the position of "Apr" within the month list.

ABCDE
1MonthFind:Apr
2JanPosition:?
3Feb
4Mar
5Apr
6May

Enter a formula in E2.

Show a hint

=MATCH(E1,A2:A6,0).

This is the exact mechanism behind INDEX/MATCH combos that look up a month, then pull a value from that month's column.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the INDEX & MATCH exercises →

More lookups practice

All Excel practice exercises · Excel test prep