Appearance
How to Use XLOOKUP in Excel
Applies to: Microsoft 365 (Excel desktop, Excel for the Web)
Article Type: How-To
Last Updated: 2026-03-09
Summary
XLOOKUP searches a range for a value and returns a corresponding result from another column or row. It replaces VLOOKUP in most scenarios and is easier to use. This article explains the syntax, provides practical examples, and compares XLOOKUP to VLOOKUP.
Prerequisites
- Microsoft 365 or Excel 2021 or later. XLOOKUP is not available in Excel 2019 or earlier.
Instructions
1. Understand the XLOOKUP Syntax
The basic formula structure:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Description | Required |
|---|---|---|
lookup_value | The value to search for | Yes |
lookup_array | The column or row to search in | Yes |
return_array | The column or row to return results from | Yes |
if_not_found | Text to display if no match is found | No |
match_mode | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard | No |
search_mode | 1 = first to last (default), -1 = last to first | No |
2. Basic Example — Look Up a Price
Find the price of a product by name.
- Assume Column A contains product names and Column B contains prices.
- In an empty cell, type:
=XLOOKUP("Widget", A2:A100, B2:B100, "Not found") - Press Enter. The formula returns the price for "Widget", or "Not found" if it does not exist.
3. Replace a VLOOKUP with XLOOKUP
XLOOKUP is a modern replacement for VLOOKUP with key advantages.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Lookup direction | Right only | Left, right, any direction |
| Column reference | Column index number | Direct column reference |
| Default match | Approximate | Exact |
| Error handling | Requires IFERROR wrapper | Built-in if_not_found argument |
VLOOKUP example:
=IFERROR(VLOOKUP("Widget", A2:C100, 3, FALSE), "Not found")XLOOKUP equivalent:
=XLOOKUP("Widget", A2:A100, C2:C100, "Not found")4. Use XLOOKUP with Multiple Return Columns
Return an entire row of data for one lookup.
- In an empty cell, type:
=XLOOKUP("Widget", A2:A100, B2:D100, "Not found") - This returns values from columns B, C, and D for the matching row.
Troubleshooting
INFO
XLOOKUP is only available in Microsoft 365 and Excel 2021+. If you share a workbook with someone using Excel 2019 or earlier, they will see a #NAME? error for XLOOKUP formulas.
| Symptom / Error | Potential Cause | Solution |
|---|---|---|
#NAME? error | XLOOKUP not supported | Verify you are using Microsoft 365 or Excel 2021+. Use VLOOKUP as a fallback for older versions. |
#N/A error | No matching value found | Check spelling and data types. Use the if_not_found argument to handle missing values gracefully. |
| Wrong result returned | Duplicate values in lookup column | XLOOKUP returns the first match. Sort data or use search_mode -1 to search from the last row. |