XLOOKUP in Excel - Tutorials

Learn how to use XLOOKUP in Excel with real-life examples. A beginner-friendly guide to replace VLOOKUP, HLOOKUP, and INDEX-MATCH easily.

If you’re tired of thinking How to fetch data from the left side using VLOOKUP or limits with VLOOKUP, it’s time to explore XLOOKUP – a modern, flexible, and easy-to-use function in Excel 365 and Excel 2021.

Think of it as a supercharged version of VLOOKUP, HLOOKUP, and even INDEX-MATCH — all in one!

Let’s break it down, learn how it works, and explore real-life examples you’ll actually use in reporting and MIS tasks.

Table of Contents

What is XLOOKUP in Excel?

XLOOKUP searches for a value in one column and returns a corresponding value from another column — either to the right, left, above, or below.

Syntax:

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

lookup_value: What you want to find
lookup_array: Where to find it
return_array: What to return
if_not_found (optional): What to return if not found
match_mode (optional): 0 for exact match (default)
search_mode (optional): 1 = first to last, -1 = last to first

Why XLOOKUP is Better than VLOOKUP

No need to count column numbers
Works left to right and right to left
Handles missing values gracefully
Can return multiple columns
Can search top to bottom or bottom to top

Example 1: Basic XLOOKUP – Find Customer Name

Imagine you have this table:

Customer ID	Customer Name
C001	Rahul Sharma
C002	Anjali Mehta
C003	Deepak Verma

Want to get the customer name using ID?

=XLOOKUP("C002", A2:A4, B2:B4)

👉 Returns: Anjali Mehta

Example 2: Product Price Lookup with “Not Found” Handling

Product Code	Price
P101	150
P102	175
P103	200

=XLOOKUP("P105", A2:A4, B2:B4, "Not Available")

👉 Output: Not Available (instead of an error!)

Example 3: Fetching Employee Department (Right to Left Lookup)

Department	Emp ID
HR	E001
Sales	E002
IT	E003

To get the Department using Employee ID:

=XLOOKUP("E002", B2:B4, A2:A4, "NA")

👉 Output: Sales

Note: Unlike VLOOKUP, XLOOKUP works from right to left!

Example 4: Get Last Attendance Status (Bottom to Top Search)

Date	Status
01-Jun	Present
02-Jun	Absent
03-Jun	Present
04-Jun	Leave

To get the last status that’s not blank:

=XLOOKUP("*", B2:B5, B2:B5, , 2, -1)

👉 Output: Leave

**What Does `*` Mean in This XLOOKUP?**

In Excel, the * is a wildcard character that means “any number of characters”. So when used as a lookup value:

* matches any non-blank cell.

Purpose of This Formula

This formula is used to:

Find the last non-blank value in the range B2:B5.

Let’s explain how it works part by part:

Formula Breakdown:

Parameter	Meaning
`"*"`	Look for any cell with content (non-blank) using wildcard `*`
`B2:B5` (lookup array)	Range to search
`B2:B5` (return array)	Range to return the value from
`,` (if_not_found)	Skipped (returns `#N/A` if nothing is found)
`2` (match mode)	Use wildcard match mode
`-1` (search mode)	Search from bottom to top (last to first)

Example 5: Multi-column Return – Name & Salary Together

Emp ID	Name	Salary
E001	Pooja Joshi	32000
E002	Arvind Kumar	40000
E003	Sneha Patel	35000

Formula:

=XLOOKUP("E003", A2:A4, B2:C4)

👉 Returns both: Sneha Patel and 35000
(Make sure to enter in two adjacent cells horizontally.)

Bonus: Dynamic Lookup Using Cell Reference

Instead of hardcoding, use a cell:

=XLOOKUP(G1, A2:A10, B2:B10, "No Match")

Where G1 has the search value.

Common Use Cases in MIS or Reporting

Search Salesperson Name based on ID
Fetch Price or Qty from Product Code
Return Leave Status for Employees
Combine with IF, TEXT, or MATCH
Create dynamic reports or dashboards

When XLOOKUP May Not Work

Not available in Excel 2016/2019 or older
Use INDEX + MATCH or VLOOKUP for backward compatibility

Tips & Tricks

Use IFERROR() around XLOOKUP if your version doesn’t support if_not_found.
Combine with MATCH() to make dynamic column selection.
You can nest XLOOKUP within other formulas like SUM, IF, FILTER.

Also Read: IFERROR in Excel

Summary Table

Feature	VLOOKUP	XLOOKUP
Search Direction	Only Left to Right	Left & Right
Column Index Needed	✅ Yes	❌ No
Handle Missing Value	❌ #N/A	✅ “Not Found”
Return Multiple Columns	❌ No	✅ Yes
Replace INDEX-MATCH	❌ No	✅ Yes

Final Thoughts

XLOOKUP is the future of lookups in Excel. If you’re using Excel 365, it’s time to leave behind the limits of VLOOKUP and embrace a more powerful, readable, and flexible function.

If you’re creating reports, MIS dashboards, or handling large datasets — mastering XLOOKUP is a must-have skill.

What’s Next?

In the next post, we’ll learn about the FILTER Function in Excel

Spread the love