Ever had to cut and paste columns because the value you needed was to the left of your lookup column? Or typed 0 or FALSE at the end of every formula, only to have the whole sheet break when someone inserted a column? You no longer need nested formulas just to pull data cleanly.
Quick Overview
- Basic formula: Enter just 3 required arguments as
=XLOOKUP(lookup_value, lookup_array, return_array)to search immediately- No direction limits: Look up data to the left of your lookup column without moving columns
- Built-in error handling: Add fallback text in the 4th argument to prevent
#N/Aerrors without usingIFERROR- Supported versions: Available in Microsoft 365 and Excel 2021 or later (older versions return
#NAME?)
What Makes the Classic VLOOKUP So Frustrating?
For years, VLOOKUP has been the go-to lookup function in Excel. But its structural limitations often caused unnecessary delays.
The biggest issue is one-way searching. VLOOKUP requires the lookup value to be in the first (leftmost) column of the table range. It cannot retrieve data to the left of that column. It is like a bookshelf where you can only read the leftmost label and pull books from the shelves to the right.
It also requires hardcoded column index numbers like ‘3.’ If someone inserts or deletes a column in your table, the formula returns the wrong data. On top of that, its default match type is approximate match, meaning that forgetting to add 0 or FALSE at the end frequently produces incorrect results.
The Basic XLOOKUP Syntax: Just 3 Arguments
XLOOKUP drastically simplifies data lookups. Out of six total arguments, you only need the first three to get started.
The basic syntax is =XLOOKUP(lookup_value, lookup_array, return_array). Because you select the lookup range and the return range separately, it can pull values whether they sit to the left or right of your lookup column.
| Order | Argument Name | Required | Description |
|---|---|---|---|
| 1 | lookup_value | Required | The value you want to look up |
| 2 | lookup_array | Required | The range or array to search |
| 3 | return_array | Required | The range or array to return values from |
| 4 | [if_not_found] | Optional | Text to display if no match is found |
| 5 | [match_mode] | Optional | Match type (0: Exact match by default) |
| 6 | [search_mode] | Optional | Search direction (1: First-to-last, -1: Last-to-first) |
If you specify multiple columns in return_array (e.g., B2:D9), XLOOKUP supports dynamic arrays—spilling the results across neighboring cells automatically from a single formula.
Related Guides
How to Remove Duplicates in Excel Without Losing Data
How to Mask Data in Excel in 1 Second Without Complex Formulas
Advanced Features: Built-In Error Handling and Reverse Search
XLOOKUP handles errors and changes search direction natively without nesting extra functions.
Previously, you had to wrap formulas in IFERROR or IFNA to suppress #N/A errors when a value was missing. With XLOOKUP, simply provide a custom message like "Not Found" in the 4th argument, [if_not_found].
Because match_mode defaults to 0 (exact match), you can skip extra configuration in most cases. Additionally, entering -1 for the 6th argument, [search_mode], searches from bottom to top (last to first), making it easy to find the most recently added entry.
Supported Excel Versions and Common Error Codes
Because XLOOKUP is a newer function, check your Excel version before using it.
XLOOKUP is currently supported in Microsoft 365 subscriptions and standalone Excel 2021 or later. Opening a workbook containing XLOOKUP in older versions like Excel 2016 or Excel 2019 will return a #NAME? error because the function is not recognized.
| Error Code | Common Cause | Solution |
|---|---|---|
#NAME? | Opening the file in older versions like Excel 2016 or 2019 | Use a supported version (Microsoft 365, Excel 2021+) |
#VALUE! | Lookup range and return range have different row or column counts | Match the dimensions (number of rows) of both ranges |
#N/A | No match found and the 4th argument is omitted | Add fallback text to the 4th argument |
Check your current Excel version and replace cumbersome VLOOKUP formulas with the simple 3-argument XLOOKUP.
Related Articles
- How to Fill Blank Cells in Excel at Once Using Shortcuts
- How to Repeat Your Last Action Instantly in Excel with F4
- How to Add Hyphens to Phone Numbers in Excel in 3 Seconds Without Formulas
- How to Recover an Unsaved Excel File After Clicking Don’t Save
답글 남기기