Why do You Need XLOOKUP

If you use Excel at work to keep track of things like sales numbers or client information, you know how hard it can be to find exactly what you need. Usually, Excel has special tools called “lookup functions” to help with this, but traditional lookup functions like VLOOKUP and HLOOKUP often fall short due to their limitations.

XLOOKUP is a new tool that makes finding information much easier. It lets you look up data in any direction and handles complex searches better. It’s available for people using Microsoft 365 and Office 2021 and makes working with big lists of information much simpler and faster.

How does XLOOKUP Work in Excel

To explain the Excel XLOOKUP function properly, we’ll use an example scenario. We have a dataset that contains information about an employee’s start date within a Division and Department.  This is on a sheet named “MD” for “Master Data”.

We would like to find an employee name from the list below and return the Division that the user was originally assigned and place the result in Column C.

We would also like to calculate the employee’s bonus based on the Yearly Sales (Column B) and place the results in Column E.

The calculations for the Bonus are based on the following table.

XLOOKUP Syntax

  • lookup: What you’re looking for: This is the information you want to find. For example, a person’s name.
  • lookup_array: Where to look: This tells Excel where to start searching. It’s like telling it, “Look in this column of names.”
  • return_array: Where to find the answer: Once Excel finds the name, it needs to know where to get the related information, like the phone number. You tell it which column has the phone numbers.
  • if_not_found: [optional] Not finding a match: Sometimes, the thing you’re looking for isn’t there. Excel can either give you an error or a message you choose, like “Not found.” It’s like choosing what to hear when your search doesn’t work out.
  • match_mode: [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
  • search_mode: [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

XLOOKUP goes step by step: it looks for the name you gave, in the place you told it to look, and when it finds that name, it grabs the phone number from the place you pointed out.

XLOOKUP Example: Exact Match

Let’s begin by looking up the user’s Division.  We’ll select the first empty cell below our Division heading and enter the following formula.

=XLOOKUP(A5, MD!$D$5:$D$37, MD!$B$5:$B$37)

The logic works like so:

=XLOOKUP(lookup_value, lookup_array, return_array)

NOTE:  There are three additional optional arguments that we will examine later in the post.

  • lookup_value – is the value we want to find (cell A5)
  • lookup_array – is the list to find the lookup_value in (sheet “MD” cells D5:D37)
  • return_array – is the list to return from upon discovery (sheet “MD” cells B5:B37)

We have “locked” the references to the lookup_array ($D$5:$D$37) and return_array ($B$5:$B$37) since we want those references to remain the same when we fill the formula down the list of names. Read more about cell references and what is the dollar sign in Excel in this article.

If you don’t want to deal with relative/absolute references, consider converting the data to a proper Excel Table to use structured references instead of traditional cell references.

Notice that we didn’t have to tell XLOOKUP to perform an exact match lookup because XLOOKUP defaults to exact match.  Unlike VLOOKUP/HLOOKUP where you had to expressly tell them to perform an exact match, we don’t need to define anything for this behavior.

Fill the XLOOKUP formula down the list to see the results.

xlookup basic lookup

💡 Notice that in the data, the column that we are returning data from is to the left of the column we are searching. This is not possible with a traditional VLOOKUP formula.

XLOOKUP Example: Search Mode

Notice in the Division results column, we have identified “Kim West” as being a member of the “Utility” Division.

If we look at the data, we see that “Kim West” appears twice.

This is because “Kim” was originally assigned to the “Utility” Division upon initial hiring but was then transferred to the “Game” Division a few years later.

Like the VLOOKUP function, XLOOKUP returned the Division for the first discovered instance of “Kim West” in the Name column.

What if we need to return the LAST assigned Division?

Discover the Last Match

Since our data is sorted in ascending order by Start Date, we can use an optional argument to perform a “reverse lookup” so we stop on the last instance of “Kim West” (we’re actually stopping on the first encountered item in the list when you search from the bottom-up.)

We will copy the formula we used to discover Division and paste it below the Current Division heading.  The formula requires the following modification:

=XLOOKUP(A5, MD!$D$5:$D$37, MD!$B$5:$B$37, "Not Found", 0, -1)

The logic of the additional arguments works like so:

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

NOTE:  The two additional arguments are optional, hence the square brackets.

  • [if_not_found] – is what to display if no match exists (e. text message or default value)
  • [match_mode] – specifies the Match Type. (0 = Exact match {default}, -1 = Exact match or next smaller, 1 = Exact match or next larger, 2 = Wildcard match)
  • [search_mode] – specifies the Search Mode. (1 = Search first to last {default}, -1 = Search last to first, 2 = Binary search {ascending}, -2 = Binary search {descending})

In our case, we are performing an exact match (0) from the last record to the first record (-1) and we will display a message (“Not Found”) if there is not a match .

We see that “Kim West” began her employment working in the “Utility” Division, but currently resides in the “Game” Division.

XLOOKUP Example: Match Mode

Next, we will discover the Bonus amount based on the value in the Yearly Salary column.

The Yearly Salary will be located in the following table and once found, will return the Bonus percentage.

Since the table establishes ranges of salaries, the odds are slim that we will search for a value that is defined in the Salary column.  Instead, we will need to return the Bonus for the Salary that is the closest without going over.

Select the first empty cell below our Bonus heading and enter the following formula.

=XLOOKUP(B5, MD!$G$5:$G$9, MD!$H$5:$H$9, "Not Found", -1)

The results are as follows.

“Kim West” has a yearly salary of 60,200.  The closest value to 60,200 without going over is $60,000.  Therefore, we return 10%.

Remember, the -1 in the [match_mode] argument means “exact match or next smaller item”.

XLOOKUP – Super Amazing Feature

In the previous example, we searched for the value “closest to without going over” our lookup value.  The table was sorted from the smallest value to the largest value, as all lookup tables are required to be when performing an approximate match lookup.

Check THIS out!  We can list the salaries in any order we want… AND IT STILL WORKS!!!

This means you can sort your lists ANY WAY YOU WISH, and the lookup still works.

Download the Workbook

Grab your free copy of the workbook to follow along 👉 HERE.

Excel Download Practice file

The XLOOKUP function in Excel has a lot more to offer than we’ve covered here. We’ve only just begun to explore its capabilities.

For a deeper dive into more advanced uses of XLOOKUP, check out this article.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.