XLOOKUP in Excel: How to Use XLOOKUP Formula for Fast Data Lookup, by Genius World on YouTube. 1 min 20 s.
- Video length
- 1 min 20 s
- Time to do it
- 5 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Click the cell where you want the result to appear, for example an empty cell beside the value you are searching for.
Type =XLOOKUP( to start the formula. Excel will show the argument names as a hint under the cell.
Enter the lookup value first. Type a cell reference like E2, or text in quotes like "A1001", then a comma.
Select the lookup array, the range that holds the value you are searching, such as A2:A100. Add a comma.
Select the return array, the range with the answer you want back, such as C2:C100. Both ranges must be the same height.
Close the bracket and press Enter. Excel returns the matching value from the return column.
Add a fourth argument to handle missing values, for example =XLOOKUP(E2,A2:A100,C2:C100,"Not found").
Leave match mode empty for an exact match. Use 0 for exact, -1 for the next smaller item or 1 for the next larger item.
Drag the fill handle down to copy the formula to other rows, after locking the ranges with dollar signs like $A$2:$A$100.
Tips and common mistakes
The lookup array and return array must be the same size, or you get a #VALUE error.
XLOOKUP works in both directions, so the return column can sit to the left of the lookup column.
Exact match is the default, so you no longer need the FALSE argument that VLOOKUP requires.
Use absolute references before filling down, otherwise the ranges shift and return wrong results.
Questions
Does XLOOKUP work in older versions of Excel?
No. It is built into Excel 365, Excel 2021 and Excel for the web. In Excel 2019 and earlier you need VLOOKUP or INDEX with MATCH instead.
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP asks for the lookup range and the return range, so you never count column numbers and it can look left or right. It also defaults to an exact match and has a built-in option for missing values.
Why do I get #N/A with XLOOKUP?
The lookup value is not found in the lookup range, often because of extra spaces or a number stored as text. Add a fourth argument like "Not found" to show a clean message instead.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
