If you’ve ever wrestled with VLOOKUP — counting columns, getting #N/A errors, or watching a formula break because someone inserted a column — XLOOKUP is the fix. It does the same job (find something in one list and bring back a matching value) but it’s simpler to write and much harder to break.
XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web.
How XLOOKUP works
At its simplest, XLOOKUP needs three things:
=XLOOKUP(what to find, where to look, what to bring back)
| Part | What it means | Example |
|---|---|---|
| lookup_value | The thing you’re searching for | E2 (a product code) |
| lookup_array | The column to search in | A2:A100 (all product codes) |
| return_array | The column to bring an answer back from | C2:C100 (all prices) |
A simple example
Say you have a price list with product codes in column A, product names in column B and prices in column C. You want to type a code into cell E2 and see its price in F2.
- Click in cell F2.
- Type
=XLOOKUP(E2, A2:A100, C2:C100) - Press Enter.
Excel finds the code from E2 in column A and returns the price from the same row in column C. Change the code in E2 and the price updates instantly.
Why it’s better than VLOOKUP
- No column numbers. VLOOKUP makes you count columns (
...,3,FALSE)). XLOOKUP just points at the column you want. - It doesn’t break when you add columns. Because you selected the return column directly, inserting a new column moves the reference with it.
- It can look left. VLOOKUP can only return values to the right of the lookup column. XLOOKUP doesn’t care where the return column is.
- Exact match by default. No more forgetting to type
FALSEat the end.
Show a message instead of #N/A
If XLOOKUP can’t find a match, it shows #N/A. Add a fourth argument to show something friendlier:
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")
You can use any text, a zero, or even leave it blank with "".
Bring back more than one column
Want the product name and the price? Select both columns as the return range:
=XLOOKUP(E2, A2:A100, B2:C100)
The result “spills” into two cells side by side. Just make sure the cells to the right are empty, or you’ll see a #SPILL! error.
Copying the formula down
If you want to copy XLOOKUP down a column, lock the lookup and return ranges so they don’t shift as you copy. Click on the range in the formula bar and press F4 to add dollar signs:
=XLOOKUP(E2, $A$2:$A$100, $C$2:$C$100, "Not found")
Now you can drag the fill handle down and every row will search the same list.
Tip: Even better, turn your list into a table first with Ctrl + T (Command + T on a Mac). Then the ranges grow automatically as you add products.
Troubleshooting
#NAME? error. Your version of Excel doesn’t have XLOOKUP. You’ll need Microsoft 365 or Excel 2021 or later — or use VLOOKUP or INDEX/MATCH instead.
#N/A even though the value is there. There’s probably an extra space, or a number stored as text. Try wrapping the lookup value in TRIM(), or check that both columns are formatted the same way.
#VALUE! error. Your lookup and return ranges are different sizes. A2:A100 and C2:C90 won’t work — make them match.
Wrapping up
Remember the three parts — what to find, where to look, what to bring back — and you’ll be writing XLOOKUPs in seconds. Once you’ve used it, you won’t go back to VLOOKUP.
