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 price with xlookup, look up a department with xlookup, look up a grade with xlookup.
3 of 10 free · no sign-up needed · graded on the formula you write
A2:A4 hold product names, B2:B4 hold prices. D2 holds a product name. Write a formula in E2 using XLOOKUP that returns the matching price.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Widget | 9.99 | Widget | ? | |
| 3 | Gadget | 14.5 | |||
| 4 | Gizmo | 7.25 |
Use =XLOOKUP(D2,A2:A4,B2:B4).
Right — XLOOKUP takes separate lookup and return ranges instead of a column index, which is often clearer than VLOOKUP.
Expected result in E2: 9.99
A2:A4 hold employee IDs, B2:B4 hold departments. D2 holds an ID. Write a formula in E2 using XLOOKUP for the department.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | E1 | Sales | E2 | ? | |
| 3 | E2 | Support | |||
| 4 | E3 | Engineering |
Use =XLOOKUP(D2,A2:A4,B2:B4).
Good — XLOOKUP's lookup range and return range don't need to be adjacent columns, unlike VLOOKUP.
Expected result in E2: Support
A2:A4 hold student names, B2:B4 hold grades. D2 holds a name. Write a formula in E2 using XLOOKUP for the grade.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Alex | A | Priya | ? | |
| 3 | Sam | B | |||
| 4 | Priya | A |
Use =XLOOKUP(D2,A2:A4,B2:B4).
Right — the two-range form of XLOOKUP is a direct, readable match.
Expected result in E2: A
A2:A4 hold product names, B2:B4 hold locations. D2 holds a product. Write a formula in E2 using XLOOKUP for the location.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Widget | Aisle 3 | Gadget | ? | |
| 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:A4 hold vendor names, B2:B4 hold payment terms. D2 holds a vendor. Write a formula in E2 using XLOOKUP for their terms.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Acme | Net 30 | Globex | ? | |
| 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:A4 hold names, B2:B4 hold phone extensions. D2 holds a name. Write a formula in E2 using XLOOKUP for the extension.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Chen | 101 | Lena | ? | |
| 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:A4 hold guest names, B2:B4 hold room numbers. D2 holds a guest. Write a formula in E2 using XLOOKUP for the room number.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Omar | 204 | John | ? | |
| 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:A4 hold discount codes, B2:B4 hold percentages. D2 holds a code. Write a formula in E2 using XLOOKUP for the percentage.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | SAVE10 | 10 | SAVE5 | ? | |
| 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 →A2:A4 hold course names, B2:B4 hold credit counts. D2 holds a course. Write a formula in E2 using XLOOKUP for the credits.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Math 101 | 3 | Art 100 | ? | |
| 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:A4 hold SKUs, B2:B4 hold bin locations. D2 holds a SKU. Write a formula in E2 using XLOOKUP for the bin.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | S1 | B12 | S3 | ? | |
| 3 | S2 | B04 | |||
| 4 | S3 | B19 |
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 →