XLOOKUP practice exercises
5 graded problems with realistic data, all hard.
A more flexible lookup than VLOOKUP. It searches one range and returns from another, in either direction. The 5 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 XLOOKUP guide first: syntax, a worked example and the mistakes people make.
In this set
- Look up a product's price with XLOOKUP
- Look up a code with a friendly fallback
- Look up leftward: something VLOOKUP can't do
- Look up an employee's title
- Look up a membership rate
The exercises
Look up SKU-22's price using XLOOKUP instead of VLOOKUP.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Item | Price | SKU: | SKU-22 |
| 2 | SKU-14 | Notebook | 3.5 | Price: | ? |
| 3 | SKU-22 | Pen | 1.2 | ||
| 4 | SKU-31 | Highlighter | 2 |
Enter a formula in E2.
Show a hint
XLOOKUP(lookup_value, lookup_array, return_array) uses two separate ranges instead of one table: =XLOOKUP(E1,A2:A4,C2:C4).
XLOOKUP is what most finance and analytics teams reach for now instead of VLOOKUP. It's more explicit about exactly which two columns are involved.
Look up code A9. It doesn't exist in the table, so show "Not Found" instead of an error.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Name | Code: | A9 | |
| 2 | A1 | Widget | Name: | ? | |
| 3 | A2 | Gadget | |||
| 4 | A3 | Gizmo |
Enter a formula in E2.
Show a hint
XLOOKUP takes an optional 4th argument for exactly this: =XLOOKUP(E1,A2:A4,B2:B4,"Not Found").
VLOOKUP needs IFERROR wrapped around it for this. XLOOKUP builds the fallback right in, one less thing to nest.
Given a Name, find its Code. But Code is in the column to the LEFT of Name, which VLOOKUP can't search backward into.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Name | Name: | Gadget | |
| 2 | A1 | Widget | Code: | ? | |
| 3 | A2 | Gadget | |||
| 4 | A3 | Gizmo |
Enter a formula in E2.
Show a hint
XLOOKUP doesn't care which side the return column is on: =XLOOKUP(E1,B2:B4,A2:A4).
This is the single biggest reason people switch from VLOOKUP to XLOOKUP: direction stops mattering.
Look up the job title for employee ID 1003.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Title | ID: | 1003 | |
| 2 | 1001 | Analyst | Title: | ? | |
| 3 | 1002 | Associate | |||
| 4 | 1003 | Manager |
Enter a formula in E2.
Show a hint
=XLOOKUP(E1,A2:A4,B2:B4).
A numeric ID lookup works exactly the same way as a text one. XLOOKUP doesn't treat them differently.
Look up the rate for the Silver tier.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tier | Rate | Tier: | Silver | |
| 2 | Bronze | 0.02 | Rate: | ? | |
| 3 | Silver | 0.035 | |||
| 4 | Gold | 0.05 | |||
| 5 | Platinum | 0.07 |
Enter a formula in E2.
Show a hint
=XLOOKUP(E1,A2:A5,B2:B5).
Rate cards are the same lookup pattern whether you write it with VLOOKUP or XLOOKUP. The payoff is flexibility, not new math.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the XLOOKUP exercises →