Excel practice · Lookups

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

  1. Look up a product's price with XLOOKUP
  2. Look up a code with a friendly fallback
  3. Look up leftward: something VLOOKUP can't do
  4. Look up an employee's title
  5. Look up a membership rate

The exercises

1. Look up a product's price with XLOOKUP Hard

Look up SKU-22's price using XLOOKUP instead of VLOOKUP.

ABCDE
1SKUItemPriceSKU:SKU-22
2SKU-14Notebook3.5Price:?
3SKU-22Pen1.2
4SKU-31Highlighter2

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.

2. Look up a code with a friendly fallback Hard

Look up code A9. It doesn't exist in the table, so show "Not Found" instead of an error.

ABCDE
1CodeNameCode:A9
2A1WidgetName:?
3A2Gadget
4A3Gizmo

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.

3. Look up leftward: something VLOOKUP can't do Hard

Given a Name, find its Code. But Code is in the column to the LEFT of Name, which VLOOKUP can't search backward into.

ABCDE
1CodeNameName:Gadget
2A1WidgetCode:?
3A2Gadget
4A3Gizmo

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.

4. Look up an employee's title Hard

Look up the job title for employee ID 1003.

ABCDE
1IDTitleID:1003
21001AnalystTitle:?
31002Associate
41003Manager

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.

5. Look up a membership rate Hard

Look up the rate for the Silver tier.

ABCDE
1TierRateTier:Silver
2Bronze0.02Rate:?
3Silver0.035
4Gold0.05
5Platinum0.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 →

More lookups practice

All Excel practice exercises · Excel test prep