Topic 5 of 15, EHL Maths & Excel final (AP)
XLOOKUP
XLOOKUP searches for one value in a reference table and returns the related information from another column. It is the standard way to bring a price, a name or a rate from a second sheet.
When the exam asks for it
- “retrieve”, “find the price of”, “bring the rate from the other table / sheet”
Formulas
=XLOOKUP(lookup_value, lookup_array, return_array)=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")=XLOOKUP(B2, Rates!$A$2:$A$4, Rates!$C$2:$C$4)=XLOOKUP(B2, Rates!$A$2:$A$4, Rates!$B$2:$B$4)Worked example
Table 1: Rates sheet. The reference table: Excel searches the room code in column A and takes the answer from column B or C.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Room code | Room type | Price (CHF) | |
| 2 | STD | Standard | 180 | |
| 3 | DLX | Deluxe | 260 | |
| 4 | STE | Suite | 420 | |
| 5 |
Table 2: Bookings sheet. The table you work in: you type the room code, XLOOKUP fills in the room type and the price (yellow cells).
=XLOOKUP(B2, Rates!$A$2:$A$4, Rates!$C$2:$C$4, "Not found")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Guest | Room code | Room type | Price (CHF) | |
| 2 | Meier | DLX | Deluxe | 260 | |
| 3 | Rossi | STD | Standard | 180 | |
| 4 | Keller | STE | Suite | 420 | |
| 5 | Weber | XYZ | Not found | Not found | |
| 6 |
Step by step
- Find the reference tablethe Rates sheet (Table 1) has the room types and prices
- Find what links both tablesthe room code: column B in Bookings, column A in Rates
- lookup_value: one cellB2 in the Bookings sheet, here DLX
- lookup_array: where to searchRates!$A$2:$A$4, the room codes
- return_array: what to bring backRates!$C$2:$C$4 for the price, Rates!$B$2:$B$4 for the room type
=XLOOKUP(B2, Rates!$A$2:$A$4, Rates!$C$2:$C$4, "Not found")260 for DLX in D2; fill down for every booking=XLOOKUP(B2, Rates!$A$2:$A$4, Rates!$B$2:$B$4, "Not found")Deluxe in C2; only the return_array changes- Row 5: code XYZ is not in Ratesthe 4th argument shows Not found instead of #N/A
⚠ Exam traps
- Lock the two reference ranges with $, otherwise they slide down when you fill the formula.
- lookup_value is a single cell, not a whole column.
- lookup_array and return_array must have the same number of rows.
- #N/A means the value was not found: check spelling and extra spaces, or add a 4th argument like "Not found".
A booking has room code STE. Which price does the XLOOKUP above return?
Show answer
✓ 420 CHF.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.