XLOOKUP
A more flexible lookup than VLOOKUP. It searches one range and returns from another, in either direction.
If you're stuck trying to pull one value from a table based on a matching ID, such as a price from a ticker, a department from an employee ID, or an FX rate from a currency pair, XLOOKUP is almost always the right tool. It replaced VLOOKUP as Excel's default lookup function because it fixes VLOOKUP's biggest limitations: it can look left as well as right, it defaults to an exact match (so you stop accidentally pulling the wrong row), and it doesn't break the moment someone inserts a column in the middle of your data.
Think of XLOOKUP as answering one question: “find this value in this list, and when you find it, give me the corresponding value from this other list.” You're not telling Excel which column number to count over to (VLOOKUP's biggest weakness); you're pointing directly at the column you want back.
Syntax
| Argument | Description |
|---|---|
| lookup_value | Required. What to search for. |
| lookup_array | Required. The single range to search in. |
| return_array | Required. The range to pull the result from. Can be to the left OR right of lookup_array. |
| if_not_found | Optional. What to return instead of an error when nothing matches. |
The mistake almost everyone makes
Forgetting if_not_found. Without it, one missing ID in a 2,000-row dataset returns #N/A, and if that cell feeds into a SUM or another formula downstream, the error propagates and silently breaks your whole model. The fix costs one extra argument:
Now a missing match gives you a clear flag instead of a broken formula three tabs away that takes twenty minutes to trace back.
A real example: pulling comps data into a valuation sheet
Say you're building a trading-comps table and you have a master list of tickers with their EV/EBITDA multiples on one tab, and a shortlist of comparable companies on another. Instead of manually cross-referencing:
This pulls the EV/EBITDA multiple for the ticker in A2, searching the full ticker column on the Master List tab, returning the matching value from column D, and flags anything that didn't match instead of failing silently. This exact pattern shows up constantly in comps analysis, and it's a favorite technical-interview question: interviewers want to see that you reach for XLOOKUP by default, understand why it's better than VLOOKUP, and, critically, that you know to handle the not-found case rather than leaving raw #N/A errors in a model you'd hand to a managing director.
Worked examples
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A100 | 3.5 |
| 3 | A200 | 1.2 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A100 | 3.5 |
Common mistakes
- Forgetting return_array must be the same size as lookup_array, even though they can be on opposite sides of it.
- Not using the if_not_found argument, then getting a raw #N/A in a report meant for other people.
- Assuming XLOOKUP requires the data to be sorted. It doesn't, for exact match.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice XLOOKUP →