How to Use XLOOKUP in Excel: Practical Step-by-Step Guide

Introduction to XLOOKUP in Excel

XLOOKUP is one of the most useful functions introduced in modern versions of Microsoft Excel. It replaces older lookup formulas such as VLOOKUP and HLOOKUP, while also solving many of their limitations. With XLOOKUP, you can search for values in a range and return matching results from another range in a simpler and more flexible way.

Busy professionals often work with spreadsheets that contain thousands of rows of data. Finding the correct value manually can be time-consuming and error-prone. XLOOKUP helps automate this process by allowing Excel to locate matching data instantly.

In this step-by-step tutorial, you will learn:

• What XLOOKUP does and why it is useful
• The structure of the XLOOKUP formula
• How to perform your first lookup
• Practical examples for real-world spreadsheets
• Common mistakes and how to avoid them
• Tips for using XLOOKUP efficiently

What Is XLOOKUP?

XLOOKUP is a function that searches a range or array for a specific value and returns a corresponding value from another range. Unlike older lookup functions, XLOOKUP can search both vertically and horizontally and does not require the lookup column to be the first column in a table.

This flexibility makes it easier to build reliable spreadsheets without rearranging your data.

Common Tasks XLOOKUP Can Solve

• Looking up employee information using an ID number
• Finding product prices from a product list
• Returning sales totals based on a specific product
• Matching customer names with order records

XLOOKUP Formula Syntax

The general syntax of the XLOOKUP function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here is what each argument means:

• lookup_value: The value you want to find.
• lookup_array: The range where Excel will search for the value.
• return_array: The range that contains the result you want to return.
• if_not_found: Optional value to return if no match is found.
• match_mode: Optional setting that controls how matching works.
• search_mode: Optional setting that defines the search direction.

In most everyday cases, you will only need the first three arguments.

Step-by-Step: Your First XLOOKUP Formula

Let’s start with a simple example. Imagine you have a list of employee IDs and names, and you want Excel to return the employee’s department.

Example Dataset

• Column A: Employee ID
• Column B: Employee Name
• Column C: Department

You want to enter an employee ID and automatically return the department.

Step 1: Identify the Lookup Value

Suppose cell E2 contains the employee ID you want to search for.

Step 2: Write the XLOOKUP Formula

Enter the following formula:

=XLOOKUP(E2, A2:A100, C2:C100)

Step 3: Press Enter

Excel searches the range A2:A100 for the employee ID in E2 and returns the matching department from C2:C100.

This is the basic pattern you will use in most spreadsheets.

Example: Product Price Lookup

Let’s look at a practical business scenario.

You manage a product list with the following structure:

• Column A: Product ID
• Column B: Product Name
• Column C: Price

If a user enters a product ID in cell F2, you want Excel to return the product price.

The Formula

=XLOOKUP(F2, A2:A200, C2:C200)

Explanation:

• F2 is the value you are searching for.
• A2:A200 is the product ID column.
• C2:C200 contains the prices to return.

When a product ID is entered, Excel immediately returns the correct price.

Handling Missing Values with IF_NOT_FOUND

Sometimes a lookup value does not exist in your dataset. Without handling this situation, Excel returns an error.

You can control this using the optional if_not_found argument.

Example Formula

=XLOOKUP(F2, A2:A200, C2:C200, "Product not found")

If the product ID is missing, Excel displays “Product not found” instead of an error message.

This approach makes spreadsheets more user-friendly, especially in dashboards or reports.

Using XLOOKUP to Search Left

One limitation of VLOOKUP is that it can only search to the right. XLOOKUP removes this restriction.

For example, imagine your dataset looks like this:

• Column A: Department
• Column B: Employee ID
• Column C: Employee Name

If you want to search by Employee ID and return the Department, XLOOKUP works easily.

Example Formula

=XLOOKUP(E2, B2:B100, A2:A100)

Even though the return column is to the left of the lookup column, the formula works correctly.

Exact Match vs Approximate Match

XLOOKUP uses exact matching by default, which is usually the safest option. However, you can also use approximate matching in certain cases, such as tax brackets or discount tiers.

Match Mode Options

• 0 – Exact match (default)
• -1 – Exact match or next smaller value
• 1 – Exact match or next larger value
• 2 – Wildcard match

Example with Match Mode

=XLOOKUP(F2, A2:A50, B2:B50, "Not found", 0)

This formula forces an exact match lookup.

Searching from Bottom to Top

Another useful capability of XLOOKUP is controlling the search direction.

For example, suppose you track daily sales and want the most recent entry for a product.

Instead of searching from the top, you can search from the bottom.

Example Formula

=XLOOKUP(F2, A2:A200, C2:C200, "Not found", 0, -1)

The final argument -1 tells Excel to search from the last row upward.

This is extremely helpful when working with logs, transactions, or historical data.

XLOOKUP with Multiple Columns

XLOOKUP can return multiple columns at once if the return array includes several columns.

Example Dataset

• Column A: Product ID
• Column B: Product Name
• Column C: Price
• Column D: Category

Formula

=XLOOKUP(F2, A2:A100, B2:D100)

This formula returns the product name, price, and category in adjacent cells.

This feature is especially useful for dashboards and reporting sheets.

Common XLOOKUP Mistakes

Even experienced Excel users sometimes run into issues with lookup formulas. Here are some common mistakes to watch for.

Using Mismatched Ranges

The lookup array and return array must contain the same number of rows or columns. If they do not match, Excel will return an error.

Incorrect Data Types

If one column stores numbers as text and another stores numbers as numeric values, Excel may fail to match them.

Always ensure your data types are consistent.

Unnecessary Full Column References

Using ranges like A:A can slow down large spreadsheets. Instead, use defined ranges such as A2:A1000.

Practical Tips for Using XLOOKUP Efficiently

• Use structured tables to make formulas easier to read.
• Name key ranges for clearer formulas.
• Combine XLOOKUP with functions like IF or SUM for more advanced logic.
• Test your lookup values to ensure they match the dataset format.
• Add an if_not_found message for better user experience.

XLOOKUP vs VLOOKUP

Many Excel users are familiar with VLOOKUP, but XLOOKUP offers several advantages.

• No requirement for the lookup column to be first
• Built-in error handling
• Ability to search both vertically and horizontally
• Optional reverse search
• Cleaner and easier formula structure

Because of these improvements, many professionals now prefer XLOOKUP for new spreadsheets.

Final Thoughts

XLOOKUP is one of the most powerful and practical functions available in modern Excel. It simplifies data retrieval, reduces formula complexity, and eliminates many of the frustrations associated with older lookup formulas.

By learning the basic structure of the function and practicing with real examples, you can dramatically improve how you work with data in Excel. Whether you are managing employee records, tracking products, or building financial reports, XLOOKUP helps you retrieve accurate information quickly and reliably.

If you regularly work with lookup formulas, investing a little time in mastering XLOOKUP will save hours of manual searching and spreadsheet troubleshooting.