How to Use XLOOKUP in Excel: A Beginner-Friendly Guide
Introduction
If you’re looking to enhance your Excel skills, mastering the XLOOKUP function is a must. XLOOKUP in Excel is a powerful and flexible formula designed to replace older lookup functions like VLOOKUP and HLOOKUP. It simplifies searching for data across tables and ranges, making your spreadsheets more dynamic and easier to manage.
This guide will walk you through everything you need to know to get started with XLOOKUP, including its syntax, practical examples, and useful tips for beginners and intermediate users alike.
What is XLOOKUP?
XLOOKUP is a lookup function introduced in Excel 365 and Excel 2019 that allows you to search a range or array and return an item corresponding to the first match it finds. Unlike VLOOKUP, it works both vertically and horizontally and does not require the lookup value to be in the first column or row.
XLOOKUP Syntax
The syntax for the XLOOKUP function is:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value you want to look up.
- lookup_array: The array or range to search.
- return_array: The array or range to return the result from.
- if_not_found (optional): Value to return if no match is found.
- match_mode (optional): Specifies match type (0-exact match, -1-exact or next smaller, 1-exact or next larger, 2-wildcard match).
- search_mode (optional): Search order (1-first to last, -1-last to first, 2-binary ascending, -2-binary descending).
How to Use XLOOKUP in Excel: Step-by-Step Examples
Example 1: Basic XLOOKUP
Suppose you have a product list and their prices, and you want to find the price of a specific product.
| Product | Price |
|---|---|
| Apple | 1.2 |
| Banana | 0.5 |
| Cherry | 2.5 |
If you want to find the price of “Banana,” use the formula:
=XLOOKUP("Banana", A2:A4, B2:B4)
This searches for “Banana” in the range A2:A4 and returns the corresponding price from B2:B4, which is 0.5.
Example 2: Handling Not Found Values
What if you search for a product that doesn’t exist? By default, XLOOKUP will return an error. To avoid this, use the if_not_found argument.
=XLOOKUP("Orange", A2:A4, B2:B4, "Not Found")
This formula returns “Not Found” if “Orange” is not in the list.
Example 3: Using Match Modes
You can perform approximate matches using XLOOKUP. For example, to find the closest smaller value:
| Score | Grade |
|---|---|
| 50 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
To find the grade for a score of 75:
=XLOOKUP(75, A2:A6, B2:B6, "No Grade", -1)
This searches for the exact match or next smaller value and returns the corresponding grade C.
Example 4: Searching Horizontally
XLOOKUP works horizontally as well. Suppose you have months in the first row and sales figures below.
| Month | Jan | Feb | Mar |
|---|---|---|---|
| Sales | 100 | 150 | 120 |
To find sales for “Feb”:
=XLOOKUP("Feb", B1:D1, B2:D2)
This returns 150.
Tips for Using XLOOKUP in Excel
- Use named ranges: For easier readability, name your lookup and return arrays.
- Combine with IFERROR: Even though XLOOKUP has an
if_not_foundargument, combining with IFERROR can add custom error handling. - Leverage match_mode: Use approximate matches for grading, pricing tiers, or ranges.
- Use search_mode for optimization: Use binary search modes on sorted data for faster performance.
- Replace VLOOKUP and HLOOKUP: XLOOKUP is more versatile and should be preferred for new formulas.
Common Errors with XLOOKUP and How to Fix Them
- #N/A: Lookup value not found and no
if_not_foundargument specified. - #VALUE!: Return array and lookup array sizes do not match.
- #NAME?: Using XLOOKUP in Excel versions that don’t support it.
To fix size mismatch errors, ensure your lookup_array and return_array are of the same length and orientation.
FAQ
What is the difference between XLOOKUP and VLOOKUP?
Unlike VLOOKUP, XLOOKUP can search both vertically and horizontally, does not require the lookup column to be first, and offers more flexible error handling and match options.
Can I use XLOOKUP in older versions of Excel?
XLOOKUP is available only in Excel 365 and Excel 2019 or later versions. Older Excel versions do not support it.
How do I perform approximate matches with XLOOKUP?
Use the match_mode argument with values like -1 for exact or next smaller, or 1 for exact or next larger matches.
Can XLOOKUP return multiple results?
XLOOKUP returns the first matching result by default. To return multiple results, consider using other functions like FILTER.
Is XLOOKUP better than INDEX-MATCH?
XLOOKUP is often easier to write and understand, combining the functionality of INDEX-MATCH with additional features and simpler syntax.
Conclusion
Mastering XLOOKUP in Excel can significantly improve your data lookup tasks by making them more efficient and flexible. Whether you’re retrieving prices, grades, or sales data, XLOOKUP simplifies the process with its intuitive syntax and powerful features. Start experimenting with XLOOKUP today to enhance your Excel skills and create more dynamic spreadsheets.