If you’ve been using Excel for a while, you already know how much time lookup functions can save. For years, users relied on VLOOKUP and HLOOKUP to find and return matching information from tables. But these older functions have limitations — they can’t look left, require sorted data for approximate matches, and often break when columns are inserted or moved.
That’s where XLOOKUP comes in. It’s one of Excel’s most powerful
modern functions, designed to replace VLOOKUP, HLOOKUP, and even some
INDEX-MATCH combinations. It allows you to search for values vertically or
horizontally, return multiple columns, and even combine it with other functions
for highly dynamic results.
In this guide, you’ll explore six examples of XLOOKUP. Each one
combines XLOOKUP with another Excel function to solve practical business
problems. These examples go beyond basic lookups — they’ll show you how to track
purchases, identify top performers, create ranked reports, and summarize
multiple results in a single formula. Let’s get started.
Table of Contents
1.
Instantly Find the Latest Purchase Made by a Customer
2. Identify the Employee with
the Highest Bonus Effortlessly
3. Find the Price of a Product
Using Two Search Conditions
4. Quickly Identify the Supplier
with the 2nd Highest Sales
5. Find the Invoice Closest to a
Specific Date
6. Display All Products of a
Supplier in a Single Cell
1. Instantly Find the Latest
Purchase Made by a Customer
Imagine you’re
running a small retail store where you record every customer’s purchase —
including the product name, the amount spent, and the purchase date. Over time,
the list grows, and manually searching for each customer’s latest purchase
becomes tiring and error-prone.
Here’s an
example:
You want to
find what product Ahmed bought most recently.
Instead of
manually scanning the list, Excel can find this automatically by combining XLOOKUP
with MAXIFS — two powerful functions that work beautifully together.
Formula:
=XLOOKUP(MAXIFS(D2:D5,
A2:A5, “Ahmed”), D2:D5, B2:B5)
Step-by-Step
Explanation:
1.
MAXIFS(D2:D5,
A2:A5, “Ahmed”) looks at the Date column and finds the most recent (maximum)
date for Ahmed.
2.
XLOOKUP
then searches for that latest date in the Date column and retrieves the
corresponding Product.
Result:
Excel returns “Monitor”,
which is Ahmed’s most recent purchase.
Why it’s
powerful:
This method updates automatically whenever new purchases are added. By
replacing “Ahmed” with a cell reference (like E2), you can create a dynamic
lookup that retrieves the latest purchase for any selected customer. This formula is
great for customer tracking, sales dashboards, and retail reports — it shows the
most recent transaction per customer instantly.
2. Identify the
Employee with the Highest Bonus Effortlessly
In another
scenario, you’re managing a list of employees, their departments, and their
annual bonus amounts. You’d like to quickly identify who received the
highest bonus without sorting the data.
Here’s the
sample table:
You want Excel
to return the name of the person with the highest bonus.
Formula:
=XLOOKUP(MAX(C2:C5),
C2:C5, A2:A5)
How It Works:
- MAX(C2:C5) finds the highest
bonus amount in the list.
- XLOOKUP searches for that bonus
in the Bonus column and returns the corresponding Employee name.
Result:
Noor — the top performer who received the highest bonus.
Why it’s
useful:
This formula eliminates the need to manually sort data or use filters. You can
easily adapt it for second-highest or lowest bonuses by replacing MAX with LARGE
or SMALL. It’s an excellent technique for HR performance reports, incentive
summaries, or dashboards that track top achievers automatically.
3. Find the Price of a Product
Using Two Search Conditions
Sometimes, one
condition isn’t enough to find the right data. Suppose you manage an
electronics store and have data for product categories, names, brands, and
prices. You might need to look up a price using both category and product name
— for example, the price of the Laptop named “Inspiron.”
Here’s the
data:
You want to
find the price of Laptop Inspiron using XLOOKUP without creating extra
helper columns.
Formula:
=XLOOKUP(“Laptop”&”Inspiron”,
A2:A5&B2:B5, D2:D5)
Explanation:
1.
“Laptop”&”Inspiron”
combines the two conditions into a single lookup string.
2.
A2:A5&B2:B5
merges both columns (Category and Product) into one searchable array.
3.
XLOOKUP
then finds the matching combined value and returns the corresponding price.
Result:
155000
Why this works
well:
This formula is ideal for multi-criteria lookups. You don’t have to rely on
helper columns or complex INDEX-MATCH combinations. It’s neat, efficient, and
ensures accurate matches — especially when products share similar names across
categories.
In real business scenarios, this method saves time when searching across multiple conditions like region and salesperson, department and employee, or supplier and product type.
4. Quickly
Identify the Supplier with the 2nd Highest Sales
In sales
management, ranking suppliers or vendors by performance is a common task. Let’s
say you track your stationery suppliers and their total sales. You want to
identify which supplier comes second in terms of total sales.
Here’s the
table:
To find the
supplier with the 2nd highest sales, use XLOOKUP with the LARGE
function.
Formula:
=XLOOKUP(LARGE(C2:C5,2),
C2:C5, B2:B5)
How It Works:
- LARGE(C2:C5,2) finds the
second-largest value in the Sales column.
- XLOOKUP matches that value and
returns the Supplier’s name.
Result:
Beta Co — the supplier with the 2nd highest sales.
Pro Tip:
Change the 2 to 1 for the top supplier or 3 for the 3rd. You can even link it
to a cell reference (like E1) for an interactive ranking display.
Why it’s
useful:
This formula is perfect for creating rank-based reports or dashboards showing
top-performing suppliers, employees, or products. It’s quick, dynamic, and
doesn’t require sorting or pivot tables.
5. Find the Invoice Closest to a
Specific Date
Have you ever
needed to find which invoice date is closest to a given target date? For
example, you want to know which invoice is nearest to 12-Jan-2024.
Here’s the
data:
You want Excel
to identify the invoice date closest to 12-Jan-2024.
Formula:
=XLOOKUP(MIN(ABS(B2:B5-DATE(2024,1,12))),
ABS(B2:B5-DATE(2024,1,12)), A2:A5)
How It Works:
1.
ABS(B2:B5
– DATE(2024,1,12)) calculates the absolute difference between each invoice date
and 12-Jan-2024.
2.
MIN(…)
finds the smallest difference, meaning the date closest to the target.
3.
XLOOKUP
then finds that smallest difference and returns the matching invoice number.
Result:
INV002 — the invoice closest to 12-Jan-2024.
Why it’s
useful:
This method is a time-saver when managing payments, delivery schedules, or
project deadlines. Instead of manually comparing dates, Excel instantly
identifies the nearest one.
You can even expand this concept by using dynamic target dates stored in a cell (e.g., DATEVALUE or TODAY()), making your formula adapt automatically to changing timelines.
6. Display All
Products of a Supplier in a Single Cell
Sometimes, you
want to summarize data more compactly — for example, listing all the products
supplied by a particular supplier in one cell instead of separate rows.
Here’s the
table:
You want Excel
to display all products from Metro in one cell.
Formula:
=TEXTJOIN(",
",TRUE,FILTER(B2:B5,A2:A5=XLOOKUP("Metro",A2:A5,A2:A5)))
Explanation:
1.
XLOOKUP
confirms that “Metro” exists in the Supplier column.
2.
FILTER
extracts all products linked to Metro.
3.
TEXTJOIN
combines the results into a single cell, separated by commas.
Result:
Paper, Pen
Why it’s
useful:
This approach is extremely handy for creating summaries or quick reports.
Instead of repeating data in multiple rows, you can summarize all related items
clearly.
You can use it
to list:
- All subjects taught by a
teacher
- All products purchased by a
customer
- All projects managed by an
employee
It keeps your data clean, compact, and visually appealing — especially useful when building dashboards or interactive reports.
Conclusion
The XLOOKUP
function is far more than a replacement for VLOOKUP or HLOOKUP — it’s a
versatile, modern Excel tool that can handle dynamic lookups, multiple
criteria, and flexible outputs.
Each of these
six expert-level tutorials shows how combining XLOOKUP with other Excel
functions — such as MAXIFS, LARGE, FILTER, TEXTJOIN, or ABS — can automate
complex tasks.
Here’s what you
can achieve with these examples:
- Retrieve the latest transaction
or purchase using XLOOKUP + MAXIFS
- Instantly find top performers
with XLOOKUP + MAX
- Perform multi-condition lookups
without helper columns
- Generate rank-based supplier
reports with XLOOKUP + LARGE
- Locate closest dates using
XLOOKUP + ABS
- Summarize multiple results in
one cell with XLOOKUP + TEXTJOIN
These
techniques turn ordinary spreadsheets into smart, interactive reports that
update automatically as your data changes.
If you’re a
business analyst, data professional, or Excel enthusiast, mastering XLOOKUP
combinations will save hours of manual work every week. It’s a small learning
curve that delivers huge productivity gains.
So next time
you open Excel, try these formulas one by one. Practice them using your own
data — sales, employees, or students — and see how effortlessly you can build
smarter spreadsheets.
Remember: The goal isn’t just to learn
formulas — it’s to make Excel work for you intelligently.
Stay tuned for
more advanced tutorials and tricks from Waqas Learning Hub — where Excel
becomes easier, smarter, and more powerful every day.
0 Comments