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
- Pull the 3rd item from a list
- Pull one cell out of a grid
- Find the 4th name on a roster
- Pull one month's spend from a row
- Pull the 5th student's score
- Find where "Clips" sits in the list
- Find an ID's position in a list
- See what MATCH returns when nothing matches
- Find where the top score sits
- Find a month's position in the year
The exercises
Return the price of whichever item sits in position 3 of the list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price | Row #: | 3 | |
| 2 | Pens | 1.2 | Price: | ? | |
| 3 | Pads | 3.5 | |||
| 4 | Clips | 0.8 | |||
| 5 | Tape | 2.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.
A small table has 2 rows and 3 quarter columns. Return the value at row 2, column 3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Row: | 2 |
| 2 | 10 | 20 | 30 | Col: | 3 |
| 3 | 40 | 50 | 60 | Value: | ? |
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.
Return whichever name sits in position 4 of the roster.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Position: | 4 | ||
| 2 | Owen | Name: | ? | ||
| 3 | Priya | ||||
| 4 | Lucas | ||||
| 5 | Maya | ||||
| 6 | Farah |
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.
Four months of spend are laid out across a single row. Return whichever month sits in the given column position.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Jan | Feb | Mar | Apr |
| 2 | 400 | 350 | 500 | 420 |
| 3 | ||||
| 4 | Column #: | 3 | ||
| 5 | Spend: | ? |
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.
Return whichever score sits in position 5 of the list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Position: | 5 | |
| 2 | Lea | 88 | Score: | ? | |
| 3 | Noah | 76 | |||
| 4 | Ines | 95 | |||
| 5 | Marco | 60 | |||
| 6 | Priya | 91 |
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.
Return the position of "Clips" within the item list, not the value, just where it is.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Find: | Clips | ||
| 2 | Pens | Position: | ? | ||
| 3 | Pads | ||||
| 4 | Clips | ||||
| 5 | Tape |
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.
Return the position of employee ID 1077 within the ID list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Find ID: | 1077 | ||
| 2 | 1021 | Position: | ? | ||
| 3 | 1042 | ||||
| 4 | 1058 | ||||
| 5 | 1077 | ||||
| 6 | 1089 |
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.
"Rome" isn't in this city list. See what MATCH actually returns when there's no match.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | City | Find: | Rome | ||
| 2 | Paris | Position: | ? | ||
| 3 | Lyon | ||||
| 4 | Nice |
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.
Return the position of the score 70 within the score list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Score | Find: | 70 | ||
| 2 | 88 | Position: | ? | ||
| 3 | 76 | ||||
| 4 | 95 | ||||
| 5 | 60 | ||||
| 6 | 92 | ||||
| 7 | 70 |
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.
Return the position of "Apr" within the month list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Find: | Apr | ||
| 2 | Jan | Position: | ? | ||
| 3 | Feb | ||||
| 4 | Mar | ||||
| 5 | Apr | ||||
| 6 | May |
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 →