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 look up a product price, look up an employee's department, look up a grade from a score table.
3 of 10 free · no sign-up needed · graded on the formula you write
A2:B4 is a table of Product Name | Price. D2 holds a product name. Write a formula in E2 that looks up D2 and returns its price.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Widget | 9.99 | Gadget | ? | |
| 3 | Gadget | 14.5 | |||
| 4 | Gizmo | 7.25 |
Use =VLOOKUP(D2,A2:B4,2,FALSE) — the FALSE ensures an exact match.
Right — VLOOKUP finds D2 in the first column and returns the value from column 2 of that row.
Expected result in E2: 14.5
A2:B4 is a table of Employee ID | Department. D2 holds an ID. Write a formula in E2 that returns that employee's department.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | E1 | Sales | E3 | ? | |
| 3 | E2 | Support | |||
| 4 | E3 | Engineering |
Use =VLOOKUP(D2,A2:B4,2,FALSE).
Good — VLOOKUP matches the ID exactly and pulls the department from the second column.
Expected result in E2: Engineering
A2:B4 is a table of Student | Grade. D2 holds a student name. Write a formula in E2 that returns their grade.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Alex | A | Sam | ? | |
| 3 | Sam | B | |||
| 4 | Priya | A |
Use =VLOOKUP(D2,A2:B4,2,FALSE).
Right — VLOOKUP pulls the grade associated with the matched student name.
Expected result in E2: B
A2:C4 is a table of SKU | Name | Price. D2 holds a SKU. Write a formula in E2 that returns the price from the third column.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | S1 | Widget | 10 | S2 | ? |
| 3 | S2 | Gadget | 15 | ||
| 4 | S3 | Gizmo | 8 |
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 →A2:B4 is a table of Product | Location. D2 holds a product name. Write a formula in E2 that returns its warehouse location.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Widget | Aisle 3 | Gizmo | ? | |
| 3 | Gadget | Aisle 7 | |||
| 4 | Gizmo | Aisle 1 |
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 →A2:B4 is a table of Name | Extension. D2 holds a name. Write a formula in E2 that returns their extension.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Chen | 101 | Diego | ? | |
| 3 | Diego | 102 | |||
| 4 | Lena | 103 |
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 →A2:B4 is a table of Course | Credits. D2 holds a course name. Write a formula in E2 that returns its credit value.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Math 101 | 3 | History 201 | ? | |
| 3 | History 201 | 4 | |||
| 4 | Art 100 | 2 |
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 →A2:B4 is a table of Vendor | Terms. D2 holds a vendor name. Write a formula in E2 that returns their payment terms.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Acme | Net 30 | Initech | ? | |
| 3 | Globex | Net 15 | |||
| 4 | Initech | Net 60 |
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 →A2:B4 is a table of Guest | Room. D2 holds a guest name. Write a formula in E2 that returns their room number.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Omar | 204 | Omar | ? | |
| 3 | Aisha | 310 | |||
| 4 | John | 118 |
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 →A2:B4 is a table of Code | Discount Percent. D2 holds a code. Write a formula in E2 that returns the discount percent for that code.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | SAVE10 | 10 | SAVE20 | ? | |
| 3 | SAVE20 | 20 | |||
| 4 | SAVE5 | 5 |
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 →