Practice by topic / Excel / Lookups
Lookups pull a value from one table into another — a price, a name, a rate. They come up in almost every Excel job interview test. These 10 exercises build up step by step, starting with find a position with match, retrieve a value with index, combine index and match for a lookup.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A5 hold a list of names. D1 holds a name to find. Write a formula in E1 that returns its position in the list using MATCH.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Alex | Priya | ? | ||
| 2 | Sam | ||||
| 3 | Priya | ||||
| 4 | Chen | ||||
| 5 | Lena |
Use =MATCH(D1,A1:A5,0) — the 0 means exact match.
Right — MATCH returns the 1-based position of the value within the range, not the value itself.
Expected result in E1: 3
A1:A5 hold a list of prices. Write a formula in B1 that returns the 3rd price in the list using INDEX.
| A | B | |
|---|---|---|
| 1 | 10 | ? |
| 2 | 20 | |
| 3 | 30 | |
| 4 | 40 | |
| 5 | 50 |
Use =INDEX(A1:A5,3).
Good — INDEX(range, position) returns the value at that position within the range.
Expected result in B1: 30
A1:A4 hold product names, B1:B4 hold prices. D1 holds a product name to look up. Write a formula in E1 that returns its price using INDEX and MATCH together.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Widget | 10 | Gizmo | ? | |
| 2 | Gadget | 15 | |||
| 3 | Gizmo | 8 | |||
| 4 | Sprocket | 12 |
Combine them: =INDEX(B1:B4,MATCH(D1,A1:A4,0))
Right — MATCH finds the row position of the product, and INDEX uses that position to pull the price from a separate range.
Expected result in E1: 8
A1:A4 hold prices, B1:B4 hold product names (name is to the right of price, so a plain VLOOKUP can't look backward). D1 holds a product name. Write a formula in E1 using INDEX/MATCH that returns the price.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 10 | Widget | Gadget | ? | |
| 2 | 15 | Gadget | |||
| 3 | 8 | Gizmo | |||
| 4 | 12 | Sprocket |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold employee names. D1 holds a name. Write a formula in E1 that returns which row number (1-based within the range) they're in using MATCH.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Chen | Omar | ? | ||
| 2 | Diego | ||||
| 3 | Lena | ||||
| 4 | Omar |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold student names, B1:B4 hold scores. D1 holds a name. Write a formula in E1 using INDEX/MATCH for the score.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Alex | 88 | Chen | ? | |
| 2 | Sam | 92 | |||
| 3 | Priya | 79 | |||
| 4 | Chen | 95 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A5 hold a ranked list of finishers. Write a formula in B1 that returns the name in 4th position using INDEX.
| A | B | |
|---|---|---|
| 1 | Alex | ? |
| 2 | Sam | |
| 3 | Priya | |
| 4 | Chen | |
| 5 | Lena |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold scores. Write a formula in B1 that finds the position of the value 95 within the range using MATCH.
| A | B | |
|---|---|---|
| 1 | 80 | ? |
| 2 | 95 | |
| 3 | 70 | |
| 4 | 88 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold vendor names, B1:B4 hold payment terms. D1 holds a vendor. Write a formula in E1 using INDEX/MATCH for the terms.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Acme | Net 30 | Initech | ? | |
| 2 | Globex | Net 15 | |||
| 3 | Initech | Net 60 | |||
| 4 | Umbrella | Net 45 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1:A4 hold SKUs, B1:B4 hold bin locations. D1 holds a SKU. Write a formula in E1 using INDEX/MATCH for the bin.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | S1 | B12 | S4 | ? | |
| 2 | S2 | B04 | |||
| 3 | S3 | B19 | |||
| 4 | S4 | B07 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →