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

Introduction to XLOOKUP in Excel

XLOOKUP is one of the most powerful lookup functions introduced in modern versions of Microsoft Excel. It replaces older functions such as VLOOKUP, HLOOKUP, and even combinations of INDEX and MATCH. With a simpler syntax and more flexibility, XLOOKUP allows you to search for values in a dataset and return matching results quickly and accurately.

If you regularly work with large spreadsheets—such as sales reports, employee lists, or inventory tables—XLOOKUP can dramatically simplify your workflow. In this tutorial, you will learn how to use XLOOKUP step by step, understand its syntax, and apply it to real-world examples.

Why XLOOKUP Is Better Than VLOOKUP

Before diving into formulas, it helps to understand why many Excel professionals prefer XLOOKUP over older lookup functions.

• No column index number required – Unlike VLOOKUP, you don’t need to count columns manually.
• Works left or right – XLOOKUP can return values from either direction.
• Built-in error handling – You can easily define what to return if a match is not found.
• More flexible search options – Supports exact match, approximate match, and wildcard searches.
• Handles vertical and horizontal lookups – Eliminates the need for both VLOOKUP and HLOOKUP.

These improvements make XLOOKUP easier to maintain and less prone to errors in real business spreadsheets.

XLOOKUP Formula Syntax

The general syntax of XLOOKUP is:

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

Explanation of Arguments

• lookup_value – The value you want to search for.
• lookup_array – The range where Excel searches for the lookup value.
• return_array – The range containing the result you want returned.
• if_not_found – Optional message if the value is not found.
• match_mode – Defines the type of match (exact, approximate, wildcard).
• search_mode – Controls search direction (first-to-last or last-to-first).

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

Example Dataset

Imagine you have a simple employee table:

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

You want to search for an Employee ID and return the employee’s name automatically.

Step-by-Step: Your First XLOOKUP Formula

Step 1: Prepare the Lookup Value

Select a cell where users will enter the ID they want to search. For example:

Cell F2 = Employee ID to search

Step 2: Identify the Lookup Column

The column containing the value you want to search is the lookup array. In our example:

A2:A100

This column contains employee IDs.

Step 3: Select the Return Column

The return array is the column containing the data you want returned. To return the employee name:

B2:B100

Step 4: Enter the XLOOKUP Formula

In the result cell, type:

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

Excel will search for the value entered in F2 within the range A2:A100. When it finds a match, it returns the corresponding value from B2:B100.

Handling Missing Values

One common issue in lookup formulas occurs when a value does not exist in the dataset. Instead of returning an error like #N/A, XLOOKUP allows you to display a custom message.

Example Formula

=XLOOKUP(F2, A2:A100, B2:B100, "Employee not found")

If Excel cannot find the employee ID, it will display the message “Employee not found” instead of an error.

Returning Multiple Columns

XLOOKUP can also return multiple values at once. For example, if you want to return both the department and salary of an employee, you can expand the return range.

Example Formula

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

This formula returns two columns:

• Department
• Salary

Excel automatically spills the results into adjacent cells.

Using XLOOKUP for Left Lookups

One limitation of VLOOKUP is that it can only search from left to right. XLOOKUP does not have this restriction.

For example, suppose your spreadsheet lists employee names before their IDs:

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

You can search for an ID and return the name using:

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

This flexibility makes spreadsheet design much easier.

Using Wildcards in XLOOKUP

XLOOKUP supports wildcard characters for partial matches.

The most common wildcards include:

• * – represents multiple characters
• ? – represents a single character

Example

If you want to search for employees whose names start with “Sam”:

=XLOOKUP("Sam*", B2:B100, C2:C100,,2)

The 2 in the match mode argument enables wildcard matching.

Searching from Last to First

Sometimes you want to find the most recent entry in a dataset. XLOOKUP allows you to search from bottom to top.

Example Formula

=XLOOKUP(F2, A2:A100, D2:D100,,0,-1)

The -1 search mode tells Excel to search from the last value to the first.

Real-World Use Cases

1. Sales Reporting

In a sales report, you may want to retrieve product prices based on a product code.

Example:

=XLOOKUP(A2, Products!A:A, Products!C:C)

This formula finds the product code and returns its price.

2. Inventory Management

XLOOKUP can quickly show stock levels when a product ID is entered.

=XLOOKUP(E2, A2:A500, D2:D500)

This helps warehouse teams monitor available inventory.

3. HR Employee Records

Human resources departments often store employee information in large spreadsheets. XLOOKUP can retrieve:

• Employee departments
• Start dates
• Salaries
• Manager names

This allows HR staff to build quick employee search tools.

Common Mistakes to Avoid

1. Mismatched Ranges

The lookup array and return array should normally have the same number of rows or columns. Mismatched ranges may produce incorrect results.

2. Incorrect Match Mode

By default, XLOOKUP performs an exact match. If you need approximate matching or wildcard searches, you must specify the correct match mode.

3. Searching the Wrong Column

Ensure the lookup array contains the values you are searching for. If Excel cannot find the value, it will trigger the “not found” result.

Practical Tips for Using XLOOKUP

• Use named ranges to make formulas easier to read.
• Combine with data validation to create searchable dashboards.
• Use structured tables for dynamic formulas that expand automatically.
• Add error messages using the if_not_found argument.

These small improvements can make your spreadsheets easier for teams to maintain.

XLOOKUP vs INDEX and MATCH

Before XLOOKUP existed, many advanced users relied on the combination of INDEX and MATCH to overcome VLOOKUP limitations.

Example:

=INDEX(B2:B100, MATCH(F2, A2:A100, 0))

XLOOKUP simplifies this process significantly:

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

The result is easier to read, easier to audit, and faster to build.

When You Should Use XLOOKUP

XLOOKUP is ideal when:

• You frequently search datasets for matching values.
• You want to replace older VLOOKUP formulas.
• You need flexible lookups across different directions.
• You want built-in error handling.

However, keep in mind that XLOOKUP is available only in newer Excel versions such as Microsoft 365 and Excel 2021 or later.

Conclusion

XLOOKUP is one of the most useful modern Excel functions for professionals working with data. Its flexible design removes many limitations found in older lookup formulas and allows you to build cleaner, more reliable spreadsheets.

By understanding the core syntax and practicing with real-world examples, you can quickly apply XLOOKUP to tasks such as employee searches, product lookups, sales reporting, and inventory tracking. Once you become comfortable with the function, it will likely replace most of your VLOOKUP and HLOOKUP formulas.

Start by practicing with small datasets, experiment with optional arguments like wildcard matching and reverse search, and gradually integrate XLOOKUP into your everyday Excel workflow.