XLOOKUP Explained With Examples

XLOOKUP is one of the most useful Excel functions for real business work because it solves one of the most common spreadsheet problems: finding a matching value in one table and returning related information from another one.

That kind of task happens constantly.

Teams need to:

• match customer IDs to customer names
• pull prices from a product list
• return department names from employee codes
• map account IDs to owners
• enrich exports with category labels
• combine information from separate sheets
• check whether records exist in another dataset

That is exactly what XLOOKUP is designed to do.

This guide explains XLOOKUP in a practical way, with clear examples, the structure of the formula, the most useful real-world patterns, common mistakes, and the reasons many users prefer it over older functions like VLOOKUP.

Overview

At its simplest, XLOOKUP searches for one value and returns a corresponding value from another range.

A basic XLOOKUP looks like this:

=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B)

This means:

• take the value in A2
• search for it in column A on Sheet2
• return the matching value from column B on Sheet2

If A2 contains a product code, and Sheet2 column A contains product codes while column B contains product names, XLOOKUP returns the correct product name.

That is the core idea:

• lookup value
• lookup range
• return range

Once you understand that structure, XLOOKUP becomes much easier to use.

What XLOOKUP does

XLOOKUP is a lookup function that finds a value in one place and returns a related value from another place.

It is useful whenever your spreadsheet data is split across multiple columns, tables, or sheets.

For example:

• you have employee IDs in one sheet and need employee names
• you have sales data with product codes and need category names
• you have invoice numbers and need payment status
• you have region codes and need region labels
• you have a list of SKUs and need current prices

Instead of manually copying and matching records, XLOOKUP lets Excel do the matching for you.

That saves time, reduces manual errors, and makes reports easier to update.

Why XLOOKUP matters so much

Lookup formulas are one of the most important parts of spreadsheet work because data is rarely stored in one perfect table.

In real reporting workflows, information is often separated across:

• exports from different systems
• master reference sheets
• pricing lists
• mapping tables
• customer lists
• staff records
• categories and code dictionaries

Without a lookup formula, users often waste time manually cross-checking values.

XLOOKUP matters because it makes that process:

• faster
• cleaner
• more flexible
• easier to read
• easier to troubleshoot

It is also important because it improves on older lookup patterns in ways that are especially useful for analysts and reporting teams.

The XLOOKUP syntax explained

The main structure of XLOOKUP is:

=XLOOKUP(lookup_value, lookup_array, return_array)

In practical terms:

• lookup_value is what you want to find
• lookup_array is where Excel should search
• return_array is where Excel should pull the answer from

A fuller version of XLOOKUP can also include optional parts such as:

• what to return if nothing is found
• match mode
• search mode

But for most users, the first three parts are the foundation.

A simple XLOOKUP example

Suppose you have this table in another sheet:

And in your current sheet, cell A2 contains: P200

This formula:

=XLOOKUP(A2,Sheet2!A2:A4,Sheet2!B2:B4)

returns:

Monitor

Why?

Because Excel:

• reads the value in A2
• looks for P200 in the first range
• returns the value from the matching row in the second range

This is the core use case for XLOOKUP.

Why XLOOKUP is easier than older lookup methods

Older Excel workflows often used VLOOKUP or combinations like INDEX and MATCH.

XLOOKUP is often easier because:

• it does not require a column index number
• it can return values from columns to the left or right
• it can handle missing results more gracefully
• the formula is easier to read
• it supports search direction options
• it works well for a wide range of lookup tasks

That does not mean older formulas are useless. But XLOOKUP is often more intuitive for modern spreadsheet work.

XLOOKUP versus VLOOKUP

VLOOKUP was widely used for years, but it has some limitations.

VLOOKUP usually requires:

• the lookup value to be in the first column of the lookup table
• a column number to specify what to return
• extra care when columns are inserted or reordered

XLOOKUP is often cleaner because it works directly with:

• one lookup range
• one return range

For example, with VLOOKUP you might need to remember that the return value is in column 3 of a table.

With XLOOKUP, you simply point to the exact return column.

That makes the formula easier to understand and maintain.

Exact match lookups

One of the most common XLOOKUP uses is exact matching.

This is what happens when you want a single exact record, such as:

• one product code
• one employee ID
• one invoice number
• one account ID
• one region code

Example:

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

This finds the exact value from A2 in the Products sheet and returns the matching value from column C.

This pattern is used constantly in:

• product reporting
• finance checks
• operational tracking
• data enrichment
• record matching

Handling missing results

One of the most useful parts of XLOOKUP is the ability to handle missing matches more cleanly.

Suppose you want Excel to show Not Found instead of a raw error when no match exists.

You can write:

=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not Found")

Now if the value in A2 does not exist in the lookup range, Excel returns Not Found.

This is extremely useful in reports because:

• it looks cleaner
• it reduces confusion
• it helps flag unmatched records clearly
• it avoids ugly error outputs in stakeholder-facing sheets

This is one of the biggest practical advantages of XLOOKUP.

A business example: mapping department names

Suppose you have a sheet with employee data:

And another sheet with reference data:

You can use:

=XLOOKUP(A2,Departments!A:A,Departments!B:B,"Missing")

to return the department for each employee.

This is a very common use case because many exports include codes or IDs but not the descriptive labels managers actually want to see in reports.

A pricing example

Imagine you have sales rows with product codes, and you want to pull the correct price from a reference list.

Your sales sheet might have:

Your pricing sheet might have:

This formula in the Price column:

=XLOOKUP(A2,Prices!A:A,Prices!B:B,"No Price")

returns the matching price for each product code.

From there, you can calculate revenue with a normal multiplication formula.

This is one of the most practical reporting uses of XLOOKUP.

Looking left or right

One of the reasons users like XLOOKUP is that it can return values from either direction.

Older lookup methods like VLOOKUP were more limited because they usually expected the lookup column to sit to the left of the return column.

XLOOKUP is more flexible.

If the thing you want to return is to the left of the lookup column, XLOOKUP can still handle it.

That makes it easier to work with real spreadsheets where the columns are not always laid out in the ideal order.

Two-way lookup ideas

XLOOKUP can also support more advanced patterns, such as looking up across one dimension and then another.

For example, you might want:

• a month from a header row
• a product from a side column
• and the intersecting value from the matrix

This is more advanced than basic beginner use, but it shows that XLOOKUP can go beyond simple one-column matching.

For most users, the important point is this: XLOOKUP scales from simple everyday lookups into more powerful reporting patterns as your spreadsheet work grows.

Searching from the bottom

Sometimes you do not want the first matching result. You want the latest one.

For example:

• the most recent status
• the latest price
• the newest transaction
• the last note entered for a record

XLOOKUP supports search modes that can help with this kind of pattern.

That makes it useful in operational sheets where repeated entries exist and the latest row matters most.

This is one of the reasons XLOOKUP is often better suited to modern spreadsheet workflows than older lookup functions.

XLOOKUP in reporting workflows

XLOOKUP is especially valuable in reporting because reporting tables often depend on enrichment.

Raw exports may contain:

• IDs
• codes
• abbreviations
• short statuses
• system labels

But leadership reports usually need:

• descriptive names
• group labels
• categories
• owners
• targets
• prices
• regions
• departments

XLOOKUP bridges that gap.

It allows teams to keep a reference table in one place and pull the correct descriptive fields into the reporting sheet.

That makes reports:

• more readable
• easier to refresh
• easier to maintain
• less dependent on manual editing

Common XLOOKUP mistakes

Even though XLOOKUP is powerful, users still run into common problems.

Using the wrong lookup range

If the lookup range does not contain the value you are trying to match, XLOOKUP cannot return the result.

Always make sure the lookup range really contains the IDs, codes, or values you expect.

Using mismatched ranges

The lookup range and return range should line up correctly.

If one range starts at row 2 and the other starts at row 3, the result can be wrong even if the formula does not obviously fail.

Hidden spaces or inconsistent values

Sometimes a lookup fails because:

• one table contains extra spaces
• a code is stored as text in one place and as a number in another
• values look identical but are not truly identical

This is especially common with imported exports and manually edited sheets.

Expecting approximate logic when you need exact logic

In many business cases, users want exact matches:

• exact customer ID
• exact product code
• exact invoice number

If the data is messy, even an exact formula may not work as expected until the source values are cleaned.

Forgetting to handle missing values

A report that fills with raw errors can confuse users and look unprofessional.

That is why using a fallback like Not Found is often a good idea.

Step-by-step workflow

If you are learning XLOOKUP, this is a practical way to approach it.

Step 1: Identify the value you want to find

Ask: What am I looking up?

Examples:

• product code
• customer ID
• employee number
• invoice ID
• SKU

This becomes your lookup_value.

Step 2: Find the lookup column

Ask: Where does that value exist in the reference table?

That range becomes your lookup_array.

Step 3: Choose the return column

Ask: What do I want back if the match is found?

Examples:

• product name
• price
• region
• owner
• department
• category

That becomes your return_array.

Step 4: Decide what should happen if nothing is found

In many business spreadsheets, it is better to return a clear label such as:

• Not Found
• Missing
• No Match
• Check Code

rather than a raw error.

Step 5: Copy the formula down the table

Once the formula works for one row, copy it through the rest of the dataset.

Then audit a few rows manually to make sure the matches are correct.

Practical formula patterns

Here are some common XLOOKUP patterns used in real sheets.

Basic exact match

=XLOOKUP(A2,Ref!A:A,Ref!B:B)

Exact match with clean missing output

=XLOOKUP(A2,Ref!A:A,Ref!B:B,"Not Found")

Return a price from a product list

=XLOOKUP(A2,Prices!A:A,Prices!B:B,"No Price")

Return a department from an employee list

=XLOOKUP(A2,Employees!A:A,Employees!C:C,"Missing Department")

These examples all reflect the same idea: find one value, return the related one.

When XLOOKUP is not enough on its own

XLOOKUP is powerful, but it is not the solution to every spreadsheet issue.

Sometimes the real problem is:

• duplicate records
• bad source data
• inconsistent IDs
• poor table structure
• missing reference data
• text-number mismatches

In those cases, the lookup formula may not be the problem. The underlying data may need cleaning first.

That is why XLOOKUP works best when paired with:

• clean table structure
• consistent source values
• controlled IDs
• good reporting discipline

FAQ

What does XLOOKUP do in Excel?

XLOOKUP searches for a value in one range and returns a related value from another range. It is commonly used to match IDs, names, codes, or categories across tables.

Is XLOOKUP better than VLOOKUP?

In many cases, yes. XLOOKUP is more flexible because it can look left or right, handle missing matches more cleanly, and work without column index numbers.

What happens if XLOOKUP cannot find a match?

If XLOOKUP cannot find a match, it can return an error or a custom message depending on how the formula is written. This makes it easier to build cleaner reports.

What should I learn first before using XLOOKUP?

Before using XLOOKUP, it helps to understand basic formulas, cell references, ranges, and how lookup tables are structured in Excel.

Final thoughts

XLOOKUP is one of the most practical Excel functions because lookup problems appear everywhere in real spreadsheet work.

Teams constantly need to match records, enrich raw exports, pull labels from reference tables, return prices, attach categories, and make datasets more useful for reporting. XLOOKUP handles those tasks in a way that is clearer and often more flexible than older lookup methods.

The most important thing is not just memorizing the formula structure.

It is understanding what a lookup really does:

• identify the value to search for
• point Excel to the correct lookup range
• return the related field you actually need
• handle missing results in a clean way

Once that logic clicks, XLOOKUP becomes one of the most valuable tools in the Excel workflow.

It saves time, reduces manual matching, improves report quality, and makes spreadsheets much easier to maintain as your data grows.