Make Lookups Easier In Excel With XLOOKUP Function
If you use Excel for Microsoft 365 and use lookup functions but have not heard of the XLOOKUP function, you need to learn about it. The XLOOKUP function can replace the following functions:
- VLOOKUP
- HLOOKUP
- INDEX/MATCH
If you aren’t familiar with lookup functions, they return some unknown information from a list of data by searching for a known piece of information in the same data source.
For example, if you had a list of NBA playoff prediction stats like those below, you could use a lookup function to search for the Hornets and return their Reg. Proj. Record.
Disadvantages of Previous Lookup Functions
Previous lookup functions had some restraints that made them more challenging to work with. For instance:
- You have to use a different function to do a vertical lookup or a horizontal lookup.
- VLOOKUP and HLOOKUP functions required the data to be sorted by the column you were searching for a value within, and the data you wanted to return had to be in a column to the left (for VLOOKUP) or row below (for HLOOKUP) the column or row containing the lookup value.
- All previous lookup functions required you to use another function within the lookup function to capture and handle any function errors.
Advantages of the XLOOKUP Function
The XLOOKUP function addresses many of the shortcomings of previous lookup functions. One of the most significant changes to the function was to split the table array (where you search and return data) into two arguments: lookup array (where you search for data) and return array (where you return data from). Here’s how this change and other aspects of the XLOOKUP function make this a more flexible function:
- The column or row you are searching for a value within can be any column or row within your data list.
- The column or row you are returning data from can be any column or row within your data list.
- The data list does not need to be sorted in any particular order.
- A new “If Not Found” argument is included so you can specify some text to return when the value you are searching for is not found in your data list.
Let’s take a look at the syntax of the XLOOKUP function.
XLOOKUP Function Syntax
It’s easy to get started with the XLOOKUP function. Just pass the three required arguments:
- Lookup Value – what you are searching for.
- Lookup Array – where you are searching from.
- Return Array – where you are returning a value from.
To perform the Hornets lookup example, I eluded to earlier to return their Reg. Proj. Record, we would use the following XLOOKUP function:
Here’s how the XLOOKUP function applies to the data list:
In the example, the text “Hornets” is searched for within the lookup array of cells B4 through B33. Once “Hornets” is found, a value is returned from the Return Array, cells E4 through E33. Since “Hornets” was found on row 13, the value on row 13 of the Return Array is returned. In other words, the value “33-39” in cell E13 is returned to the cell that contains the XLOOKUP formula.
Searching for Data to Right of Return Value
As I mentioned in the advantages section of this article, one of the improved features of the XLOOKUP function is its ability to look for data in any column or row and return data from any column or row.
As an example, let’s say you wanted to return the Current Rating value for the Clippers. In the past, you would need to use the INDEX and MATCH functions to accomplish this. You could not do this with a simple VLOOKUP because the Current Rating column is to the left of the lookup column, Team.
Since the lookup and return arrays are separate arguments with the XLOOKUP function, we can easily accomplish our lookup with this function. To do a search for the Current Rating of the Clippers, we would use this formula:
Doing a Horizontal Lookup
Another great advantage of the XLOOKUP function is that you can use it for a vertical or horizontal lookup. You no longer need to use two different functions to accomplish both of these tasks.
In our previous examples, we did vertical lookups. In this example, we’ll use the XLOOKUP function to help calculate the room rate for different days of the week. If a visitor stays at our rental on Monday through Wednesday, there is no increase to the rate. However, Thursday and Sunday nights have a 10% increase, and Saturday and Sunday nights have a 20% increase.
Here are how the room rates are calculated by looking up the Increase Percentages and filling it in the Increase column, then calculating the Room Charge for the day:
And here is the formula to perform the calculation in cell C9:
The formula looks at the day of the week in cell B9, then uses cells B5 through H5 to find the day’s increase and returns data from cells B6 through H6. Notice that the lookup array and return array are formatted as absolute cell references (because of the dollar signs); this allows us to reuse the formula by copying it to cells B10 through B15. The lookup value for the day of the week will update for each new formula because of the relative cell reference (B9), but the lookup array and return arrays will remain the same no matter where we copy the formula.
In the last few sections of this article, I’ll show you how to use the optional arguments of the XLOOKUP function.
Handling Lookup Value Not Found Error
Excel would return an error with the other Excel lookup functions if the lookup value was not found. You could use another function to catch the error and change the output, or you could display the error, #N/A.
The XLOOKUP function handles this situation by adding the If Not Found argument, which is the fourth argument of the function. To use this argument, just enter the text you would like returned if the lookup value is not found in your data.
In the example below, we use an XLOOKUP function in cell B4 to return the Product Number from cells B7 through B14 by looking for the Product Name entered in cell B3 from within the range of cells A7 through A14. When we look for Staples, we get the Product Number 27-8900-88:
The XLOOKUP function used in cell B4 was:
However, if we look for a Product Name that doesn’t exist, Notebooks, for instance, we will see the text we entered in the fourth argument of the XLOOKUP function (Not Found) is returned as the result:
Match Modes
The fifth argument of the XLOOKUP function is the Match Mode. With this argument, you can specify what type of match Excel does. There are four match modes:
- 0 = Exact
- -1 = Exact or Smaller
- 1 = Exact or Larger
- 2 = Wildcard
If the match mode is not specified, the Exact match mode will be used.
The Wildcard option lets you use a lookup value with these wildcard characters:
- * = Any number of characters.
- ? = Any single character.
- ~ = An escape code that lets you specify an asterisk, question mark, or tilde as text to match.
The Exact or Larger option looks for an exact match. If it can’t find one, it looks for the next largest value in your list. The Exact or Smaller option looks for an exact match. If it can’t find one, it looks for the next smallest value.
As an example of the Exact or Smaller match mode, consider the data below:
The data has a lookup table with sales levels along with a percentage of commission that will be given to a Sales Rep. based on their Sales. You can use an XLOOKUP function in the Comm. % column to return the percentage of commission the Sales Rep. should get.
Here is the formula we would use to accomplish this:
The function:
- Uses the value in cell B9 as the lookup value.
- It looks for the lookup value in cells C3 through F3
- It will return a value from cells C4 through F4.
- If the lookup value is not found, it will return a “0”.
- The last argument specifies -1 as the match mode, so if the value is not found exactly, the next closest Sales Level is used, and that percentage of commission is returned.
So, for cell C9, we get 2% since the Sales were less than $5,000. The next smallest Sales Level is $1,000, which has a Commission % of 2%.
Search Modes
The final argument of the XLOOKUP function is the Search Mode. This argument determines if the function starts at the top or bottom of the table array to find a match of the lookup value.
There are four Search Modes:
- 1 = First-to-Last
- -1 = Last-to-First
- 2 = Binary, Ascending
- -2 = Binary, Descending
The last two options above are rarely used since Excel changed how things work in the background to speed up non-binary searches. The other two options start the search at the top of the table array or at the bottom of the table array, respectively.
Conclusion
The XLOOKUP function is an excellent addition to Excel’s lookup and reference functions. I love its flexibility and will be using it in place of the other Excel lookup functions for most (if not all) of the lookups I perform.
You can do a lot more with the XLOOKUP function, but I hope this quick primer helps you get started with it.
What do you think about XLOOKUP? Will you be using it in your next Excel project that requires a lookup function?
Other Excel Tips and Tricks:
AutoFill and Flash Fill – 2 Excel Features You Need to Know
How to Automate Repetitive Tasks Using Excel
25 Excel Navigation and Selection Shortcuts You May Not Know
5 Excel Shortcuts to Drastically Speed Up Data Entry
3 Ways Excel Can Help You Track Your Freelance Income and Expenses
Top Excel Features Every Freelancer Needs To Know About
Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.
