XLOOKUP is a flexible Excel lookup function used to find a value in one range and return the corresponding value from another range. It can replace many common VLOOKUP and INDEX/MATCH tasks and is easier to read in many modern Excel workbooks.
What Does XLOOKUP Do?
Suppose you have a product ID in one table and want to return the product name from another column. XLOOKUP searches for the ID and returns the matching value.
Basic XLOOKUP Syntax
The first three arguments are the core of the function:
- lookup_value: What you are searching for.
- lookup_array: Where Excel should search.
- return_array: The values Excel should return.
Simple Example
If product IDs are in A2:A100, product names are in B2:B100 and the ID to find is in E2:
Excel searches column A for the value in E2 and returns the corresponding product name from column B.
Return a Friendly Message When No Match Exists
The fourth argument lets you specify what should appear when the lookup value is not found:
This is often easier to read than displaying an error message in a report.
Exact Match Is the Normal Starting Point
XLOOKUP uses exact matching by default. This is useful for IDs, names, codes and other values where you want the lookup value to match the source value.
Approximate Matching
XLOOKUP also supports match modes for approximate matching. This can be useful with threshold tables, pricing bands or grading boundaries, but the lookup data must be structured correctly.
For example, a table of minimum scores and grades can be used with an appropriate approximate-match mode.
XLOOKUP Can Look Left
Unlike the traditional VLOOKUP pattern, XLOOKUP does not require the return column to be to the right of the lookup column. You specify the lookup and return arrays independently.
Why XLOOKUP Is Often Easier to Maintain
The formula explicitly identifies the search range and return range. You do not need to count a column index number as you do with the classic VLOOKUP pattern. This can make formulas easier to understand when columns are inserted or moved.
Common XLOOKUP Problems
Extra spaces
Text values that look identical can contain hidden leading or trailing spaces. Clean the source data when appropriate.
Numbers stored as text
An ID stored as text may not match an ID stored as a number. Check the underlying data types.
Duplicate lookup values
If the lookup value appears more than once, XLOOKUP returns the first matching result under its default search behavior. If duplicates are meaningful, decide which record should be returned before relying on the formula.
Wrong range sizes
The lookup and return arrays should correspond correctly. A mismatched structure can produce incorrect results or errors.
XLOOKUP vs VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Exact match | Default | Must be specified |
| Lookup direction | Flexible | Traditional left-to-right pattern |
| Not-found message | Built in | Usually wrapped with IFERROR |
| Column index number | Not required | Required |
When XLOOKUP Is Not Available
Older Excel versions may not support XLOOKUP. In that situation, VLOOKUP, HLOOKUP or INDEX/MATCH may be appropriate depending on the workbook.
Practical Checklist
- Identify the exact lookup value.
- Check whether IDs are stored consistently.
- Use exact matching unless approximate matching is intentional.
- Decide what should happen when no match exists.
- Check for duplicates.
- Verify the returned result against the source table.
For more spreadsheet workflows, explore Taskvora's Excel Tools and our Excel analysis guide.
More Excel help: See IF in Excel, SUMIF and SUMIFS, COUNTIF and COUNTIFS, INDEX and MATCH, Remove Duplicates and PivotTables.
Related Excel guide: How to Clean Data in Excel.
Related Excel guide: How to Split Text into Columns in Excel.
Related Excel guide: How to Use FILTER in Excel.