Excel XLOOKUP Explained
(Goodbye VLOOKUP!)
In this post, we will examine Excel’s BEST function for performing a lookup…
We’re going to look at how the new (upcoming) XLOOKUP function solves common lookup problems typically solved by older functions like…
- LOOKUP
- VLOOKUP
- HLOOKUP
- INDEX/MATCH
This will be just a glimpse of some of the many and varied uses of the XLOOKUP function. We will explore many more uses of XLOOKUP in future videos.
Our Lookup Challenge
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.
Basic Lookup
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.
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.
Did you see the hidden awesomeness?
Notice that in the data, the column that we are returning data from is to the LEFT of the column we are searching.
This would be impossible with a traditional VLOOKUP function (without performing some crazy in-memory, virtual table construction which only 9 people on planet Earth find enjoyable.)
Finding the LATEST Division Assignment
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.
Get the Next Smallest Value
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.
Not So Final Thoughts
There is a tremendous amount of potential lurking behind the new XLOOKUP function. This tutorial hasn’t even scratched the surface when it comes to demonstrating XLOOKUP’s potential.
We will explore many other ways to use XLOOKUP in future tutorials. For now, give it a try and be amazed.
