Return the product name
Which product name belongs to the ID in E2?
Formula: =XLOOKUP(E2,A2:A5,B2:B5,"Not found") → Chair
Hands-on Excel exercises
XLOOKUP Practice
Practice XLOOKUP in a live spreadsheet. Work through exact matches, missing values, two-way lookups, and lookup exercises with answers.
XLOOKUP becomes dependable when you stop thinking of it as a long formula and start seeing its parts: the value to find, the one-dimensional lookup range, and the equally sized return range. The live spreadsheet below uses a small catalog. Change the key, edit the formula, or deliberately request an ID that is not present to see how the result behaves.
The related XLOOKUP practice tasks build outward from that example. You will compare it with VLOOKUP, combine INDEX and MATCH, retrieve data from another sheet, and work through a mixed lookup set. Exact matching is the right default for IDs, codes, and names. Approximate matching is useful for ordered bands such as commission or grade thresholds, but it requires a different mental model and careful source data.
The worked answers focus on checks that catch quiet mistakes: matching array sizes, a stable key column, a deliberate not-found result, and test keys from different parts of the table. Those habits matter more than memorizing argument positions. For a broader hiring-focused sequence, follow the Excel interview preparation path after these tasks; it places lookups alongside cleanup, auditing, and business analysis questions.
Your turn
Your turn: in F2, return the price for the product ID in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Find ID | ||
| 2 | P101 | Lamp | 18 | P103 | ||
| 3 | P102 | Desk | 45 | |||
| 4 | P103 | Chair | 24 | |||
| 5 | P104 | Shelf | 32 |
Not yet solved.
Answer: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")
Related exercises
Open a task in its lesson.
- In F2:F5, look up the item name for each SKU. If the SKU does not exist, show "Not in catalog".
- In G2:G5, look up the price, and show 0 for an unknown SKU.
- In F2:F4, look up the item name for each code in column E.
- In G2:G4, find the employee ID for each name. The ID is to the left of the name, so VLOOKUP will not help.
- In I2:I4, find the shipping cost for the city and weight in the table on the left.
- In D2:D5, pull the price from the "Prices" sheet.
- In D10:D13, look up the course name by code (exact-match VLOOKUP).
- In G10:G13, calculate the amount to pay: price × months × (1 − discount).
Worked answers
Try the spreadsheet first, then compare your formula and result.
Find another price
What price belongs to product ID P104?
Formula: =XLOOKUP("P104",A2:A5,C2:C5,"Not found") → 32
Frequently asked questions
Why use XLOOKUP instead of VLOOKUP?
XLOOKUP separates the lookup and return arrays, can return values to the left, and provides an explicit not-found argument.
Should I use exact match?
For IDs, names, and codes, exact match is usually the safe choice. Approximate modes are for ordered thresholds and require deliberate setup.
Can XLOOKUP return several columns?
In modern Excel, the return array can span multiple columns and spill results. The linked tasks on this page practice single-column returns.