How to Use XLOOKUP in Excel: Complete 2027 Guide
XLOOKUP is one of the most useful Excel functions for finding and matching data. It can help you search for a value in one range and return a related result from another range, making it useful for reports, pricing sheets, customer records, employee lists and everyday business spreadsheets.
If you work with lists, reports, pricing tables, employee records or customer data, XLOOKUP can save time and reduce manual checking.
It is commonly used as a more flexible alternative to VLOOKUP because it can search in different directions, return a custom not-found message and work with exact matches by default.
In this guide, we explain how XLOOKUP works, how to write the formula, common mistakes to avoid and where it fits in your Excel learning pathway.
TL;DR: XLOOKUP is one of the most useful formulas to learn after basic Excel functions.
It is ideal for users who already understand simple formulas and want to work more efficiently with business data.
1) What Is XLOOKUP in Excel?
XLOOKUP is an Excel function used to find a value in one range and return a related value from another range.
For example, you could use XLOOKUP to enter a product code and return the matching product name, price or category. You could also use it to enter an employee ID and return the matching employee name, department or manager.
XLOOKUP is especially useful because it can search vertically or horizontally, return values from columns to the left or right, and show a custom result when no match is found.
Practical read: If you regularly search for matching data between tables, XLOOKUP is a formula worth learning.
2) XLOOKUP Syntax Explained
The full XLOOKUP syntax is:
For most day-to-day Excel users, the first three arguments are enough to start using XLOOKUP.
3) How to Use XLOOKUP Step by Step
The simplest way to understand XLOOKUP is to break the formula into three questions:
This is your lookup value, such as a product code, employee ID or invoice number.
This is your lookup array, such as a column of product codes or employee IDs.
This is your return array, such as a column of prices, names, categories or departments.
Basic example
Imagine you have a product list with product codes in column A and product names in column B. If the product code you want to find is in cell E2, the formula would be:
Excel looks for the value in E2, searches for it in A2:A100, and returns the matching product name from B2:B100.
4) Practical XLOOKUP Examples
Example 1: Return a price from a product code
Use XLOOKUP to enter a product code and return the matching price:
This formula searches for the product code in E2, looks through A2:A100, and returns the matching price from C2:C100. If there is no match, Excel returns “Product not found”.
Example 2: Search to the left
Unlike VLOOKUP, XLOOKUP can return a value from a column to the left of the lookup column.
This could search for an employee ID in column C and return the employee name from column A.
Example 3: Use XLOOKUP with approximate match
Approximate matching is useful for scores, pricing bands, discount levels or commission tiers.
The match mode -1 tells Excel to find the exact match or the next smaller item.
Example 4: Use XLOOKUP with wildcards
Wildcards are useful when you want to match partial text.
The match mode 2 enables wildcard matching. This formula searches for text containing “Sydney”.
Example 5: Search from bottom to top
If a list contains multiple matches, you may want to return the latest match instead of the first match.
The final argument -1 tells Excel to search from last to first.
5) XLOOKUP vs VLOOKUP
XLOOKUP is often easier and more flexible than VLOOKUP for modern Excel workbooks.
VLOOKUP is still widely used, especially in older spreadsheets. However, XLOOKUP is usually the better option for users working in current Microsoft 365 versions of Excel.
6) Common XLOOKUP Mistakes
- Different range sizes: The lookup array and return array should be the same size.
- Numbers stored as text: Excel may not match values if one value is stored as text and the other is stored as a number.
- Extra spaces: Hidden spaces can stop exact matches from working correctly.
- Duplicate values: XLOOKUP usually returns the first match unless you change the search mode.
- Very large whole-column references: Whole-column formulas can slow down large workbooks.
If your XLOOKUP formula is not working, check the source data first. Many XLOOKUP errors are caused by inconsistent formatting, hidden spaces or mismatched range sizes.
7) Where XLOOKUP Fits in Your Excel Learning Pathway
XLOOKUP is usually an intermediate Excel skill. It is a good next step once you are comfortable with basic formulas such as SUM, AVERAGE and IF.
If you are already using Excel regularly but want to work faster and reduce manual checks, Excel Intermediate is usually the right place to build confidence with XLOOKUP.
Learn Excel with Nexacu
Nexacu’s Excel courses are designed for professionals who want to improve practical spreadsheet skills and apply them directly at work.
If you are new to Excel, start with Excel Beginner. If you already use formulas and spreadsheets regularly, Excel Intermediate is a good next step. If you want to work with advanced formulas, analysis tools and reporting techniques, Excel Advanced may be the right fit.
Our instructor-led Excel training is available live online and in person, with courses designed around practical workplace examples.
8) FAQs
These are some of the most common questions people ask when learning XLOOKUP in Excel.
What is XLOOKUP in Excel?
XLOOKUP is an Excel function used to search for a value in one range and return a matching value from another range.
What is the basic XLOOKUP formula?
The basic formula is =XLOOKUP(lookup_value, lookup_array, return_array).
Is XLOOKUP better than VLOOKUP?
For many modern Excel tasks, XLOOKUP is more flexible than VLOOKUP because it can search left or right, uses exact match by default and does not rely on column index numbers.
Why is my XLOOKUP not working?
XLOOKUP may not work if the lookup value does not exist, the lookup range and return range are different sizes, numbers are stored as text, or there are hidden spaces in the data.
Can XLOOKUP search to the left?
Yes. XLOOKUP can search one column and return a value from another column to the left or right of the lookup column.
Which Excel course teaches XLOOKUP?
XLOOKUP is usually covered in intermediate or advanced Excel training. Nexacu’s Excel Intermediate and Excel Advanced courses are suitable for professionals who want to build stronger formula, data analysis and reporting skills.
Build Faster, Cleaner Excel Workbooks
XLOOKUP is a practical Excel skill that can help you reduce manual searching, clean up reports and work more confidently with business data.
Once you understand how it works, you can use it across reports, pricing sheets, dashboards, employee records, customer lists and many other workplace spreadsheets.
Learn XLOOKUP and other practical Excel formulas
Explore Nexacu’s Excel Intermediate and Excel Advanced courses to build stronger formula, reporting and data analysis skills.
- Write formulas with more confidence
Use functions like XLOOKUP, IF and logical formulas.
- Work faster with business data
Reduce manual searching and checking.
- Build more reliable reports
Create spreadsheets that are easier to update and maintain.
Nexacu is a Lumify Group company, delivering Microsoft, Adobe, data analytics, AI tools and professional development training to teams across Australia.
