Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
XLOOKUP
It finds Earbuds in the price list's Product column and brings back the price next to it.
Try this lesson free →Example
The prices live in a separate table. XLOOKUP fetches the right one:
=XLOOKUP(B2,F2:F5,G2:G5)| Order | Product | Qty | Price | Product | Price | Look up | Price | |
|---|---|---|---|---|---|---|---|---|
| O101 | Earbuds | 2 | Earbuds | 35 | Charger | |||
| O102 | Charger | 1 | Charger | 15 | ||||
| O103 | Phone case | 3 | Phone case | 8 | ||||
| O104 | Screen guard | 4 | Screen guard | 5 | ||||
| O105 | Earbuds | 1 |
Key points
B2What to look for: this order's product.
F2:F5Where to look: the price list's Product column.
G2:G5What to bring back: the matching price.
VLOOKUPThe older version, still in lots of files: =VLOOKUP(B2,F2:G5,2,FALSE). The whole table, the column number to bring back, and FALSE for an exact match.
Put together: =XLOOKUP(B2,F2:F5,G2:G5). Older files use =VLOOKUP(B2,F2:G5,2,FALSE), which does the same job.
Practice
1. In D3, look up the price for order O102's product.
=XLOOKUP(B3,F2:F5,G2:G5)2. In D4, look up the price for order O103.
=XLOOKUP(B4,F2:F5,G2:G5)3. Now the older way. In D5, look up order O104's price with VLOOKUP.
=VLOOKUP(B5,F2:G5,2,FALSE)4. In D5, look up the price for order O104.
=XLOOKUP(B5,F2:F5,G2:G5)5. In I2, look up the price of whatever product is typed in H2.
=XLOOKUP(H2,F2:F5,G2:G5)Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →