XLOOKUP
XLOOKUP looks for a value in one column and returns the data from the same row in another column, to the right or the left: =XLOOKUP(E2,B2:B9,C2:C9).
Select any words here to ask your Maestro about them.
P‑108 isn't in the table anymore. What does =XLOOKUP("P‑108",B2:B9,C2:C9) return?
What it's for: bringing in data from another table by its code: the price for each product on your orders, each customer's name from their number. It's the newer way to do what VLOOKUP does.
Why it beats VLOOKUP: you give it the column to search and the column to return, so it can return a column to the left of the code, and inserting columns doesn't make the formula point at different data.
When a code doesn't exist, decide what you want to see: it's an edge case. And compare a few rows by hand, a spot check, before you trust the whole column.
Practice it in Try to make it fail, with a lookup formula an AI gave you.
How it's used
How to write it
The price for the code in E2
- marked: =XLOOKUP(E2,B2:B9,C2:C9,"Not found")
- E2What you're looking for: the code.
- B2:B9Where it looks: the code column, wherever it sits.
- C2:C9What it returns: the price column, the same height.
- "Not found"Optional: what it shows when the code isn't there.
When to look closer
The price for P-105
E2 holds P-105
- marked: =XLOOKUP(E2,B2:B9,C2:C9)
Your formula gives $3.79 for P-105. How would you know if there's another P-105 further down?
Ask your Maestro about XLOOKUP
It answers from the free course page closest to this word, the way it answers while you study.
You: What does "XLOOKUP" mean, in plain words?
Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which VLOOKUP at work would you switch first?
Asking about: Answers from the page "How do I count the missing values in each column with pandas?"
Only this browser keeps what you ask here. How it works
Learn more
Related terms
- VLOOKUP
VLOOKUP looks for a value in a table's first column and returns the data from another column in that row: =VLOOKUP(E2,A2:C9,3,FALSE).
- Edge case
An input at the edge of what the logic expects, like a code that isn't in the lookup table, to see that it fails the way it should instead of returning a plausible wrong value.
- Spot check
Taking a few rows spread across the data (from the start, the middle and the end) and comparing them by hand with what you expect.
- Pivot table
A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.