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.

Availability

As of the writing of this post (September 2019), the XLOOKUP function is only available to Office 365 users who are members of the “Office Insiders” channel.  XLOOKUP is soon to be released to all Office 365 users, so keep checking those Office updates.

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 two 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, 0, -1)

The logic of the additional arguments works like so:

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

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

  • [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 from the last record to the first record.

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, -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.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Learn when you like, where you like.

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials

6 Comments

  1. Awadh September 12, 2019 at 11:45 am - Reply

    thanks a lot Mam
    i, m using 2010 office ver. so thought it may worke with. me. anyway i never used 365 ver. yet

  2. Khaled Sayyary September 12, 2019 at 12:13 pm - Reply

    Thank you very much, Leila, for your wonderful explanation, greetings from Sana’a – Yemen

  3. CybernautWA September 13, 2019 at 3:30 pm - Reply

    Where is the sample of example opposed to laboriously typing it out???

    • Chris September 16, 2019 at 6:19 pm - Reply

      Do you mean the practice workbook? It’s available in the post. Just scroll down to the bottom.
      Best regards

  4. Laizer September 14, 2019 at 8:55 pm - Reply

    Hi
    I’m excel Insider but i cant Xlookup in not working in my excel, what should I do?

    • Chris September 16, 2019 at 6:17 pm - Reply

      Hi – please check your Insider level. You need to be in Insider Fast (not the monthly targeted channel).
      Hope this helps.

Leave A Comment

Share This