If you’re looking for ways to walk past countless hours of skimming through large data sets only to find out something specific, XLOOKUP is here to save all of your time and effort.
With a few clicks on your keyboard, you can tell Excel what you’re looking for and XLOOKUP is going to find that precise information out instantly!
While the learning curve is somewhat balanced between easy to comprehend but a little challenging to implement, giving it some time should classify you as the master within just a week!
I’ve covered everything from the basics to the more complex challenges you can sort out by taking refuge in XLOOKUP masterclass.
If you run into any error during your workflow, you’ll probably find the solution inside this all-encompassing article on Excel XLOOKUP!
Contents
What Is Excel XLOOKUP?
Excel XLOOKUP arrived in the next generation Microsoft Office packages as a core function that’s built to overcome the limitations of VLOOKUP, HLOOKUP and LOOKUP functions.
It serves a wide array of purposes within one function whereas you’d have to implement a total of five different older functions to generate the same outcome.
This function can search across the data set horizontally, vertically or any particular way you really need Excel to work.
The arguments embedded in this function are very handy and literally takes care of all types of searches within a large chunk of data sheet efficiently.
Which Excel Versions Have XLOOKUP?
XLOOKUP is a very recent addition to the Excel family. Not all the Excel versions contain these, only a handful of recent varieties are lucky enough to have it built into their arsenal.
The Excel versions having XLOOKUP by default are as follows:
- MS Excel 2021 or later (PC and Mac)
- MS Excel for Microsoft 365 Subscriptions (PC and Mac Versions)
- MS Excel Online
- MS Excel for Tablets (iPad and Android)
- MS Excel for Mobile (iPhone and Android)
Apart from these, you won’t be directly able to use XLOOKUP features on your Excel.
How To Use XLOOKUP In Older Excel Versions?
If your Excel version is an older one and you can’t find XLOOKUP in it, don’t worry, we got you covered. Excel has the ability to install add-ins and XLOOKUP can be included in the previous versions without any difficulty!
The procedure is very easy. Simply download this file and open it. Your Excel will turn on and it’ll ask you to turn your Macros on. Once done, you’ll find a new tab called DataFX.
If you see that, your job is done! Only difference between XLOOKUP with this variant is that, you’ll have to use =DXLOOKUP instead of =XLOOKUP during use, the rest of the arguments and functionality are identical!
XLOOKUP Argument Breakdown
Let’s take a look at the argument for the XLOOKUP function so that we can understand the way it works and the extent to which it can ease up your workflow with your data set.
=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
The aforementioned syntax is how XLOOKUP works. I’ll break each of the arguments down for further clarity.
XLOOKUP: this is the base formula line that’ll tell Excel you’re using the XLOOKUP function.
lookup: the target value that you’ll be searching for within your data set. You can mention the cell ID while typing in the formula or input the text string itself within “ ”.
lookup_array: the lookup array identifies the column or row from which you’re looking for the target data or the ‘lookup’ value. Basically, you’ll have to tell Excel that the data I’m looking for exists within this area.
return_array: the return array dictates the column or row from which you’re seeking to derive data. It means, you’re asking Excel to match your target data and reply with corresponding data from the area you’ve highlighted.
not_found: as the older LOOKUP functions didn’t have any textual outcome for data the function can’t find and simply show #N/A error, XLOOKUP has room for an output where you can type in the text you’d want Excel to deliver if there’s no relevant data.
The not_found argument is optional, but it’ll still show #N/A if you don’t put anything within the “inverted commas”.
match_mode: the match mode argument establishes the matching behavior, whether it’s going to be an exact match, an exact match toward the next larger value or toward the next smaller value.
Also, it has the capacity to include the wildcard searches as well that’s precisely made for such tasks. This argument is set to exact match by default. Let me walk you through the details.
- 0: look for an exact match. It’s the default argument and will return you #N/A error if you leave the not_found blank.
- -1: either an exact match or the next smaller item.
- 1: either an exact match or the next larger item.
- 2: Wildcard match
This way, you’ll be able to tell Excel exactly how the matching should go about.
search_mode: search mode commands Excel on where to start the search from and how to proceed about it. The behaviors are determined by numeric digits as well and I’ve broken them down below for you:
- 1: this is the default setting where Excel will start searching from the very first value in the selected arrays
- -1: it’s the inverse of the default system where it’ll carry out the search from the last value
- 2: this is a small cheat-trick to search in ascending order for the binary values
- -2: similar to the previous one, it searches up in the descending order for the binary values
One thing you should note down is that, while these searches from the binary values are made out to be more swift, applying these on data that aren’t sorted may generate perfectly fine-looking results which are not accurate at all.
Applying XLOOKUP: Step-By-Step Tutorial
If you’re new to applying formulas in Excel, this guideline should help you with your first step in achieving a basic search using the XLOOKUP function. I’ve broken it down to brief & comprehensible steps for your convenience.
Step 1: Sort Your Data
Sorting out your data is never a waste of time. And when it comes to applying a formula, it becomes more pressing because a data-sheet with random data can easily provide you with a false outcome.
To do this, you can apply Filters to your data set or put everything inside an Excel table. Create column headers and apply colors to make it look more distinct for yourself too.
Make room for the output as well so that anyone who goes through your data sheet can easily understand what you’ve produced and the meaning of it.
Step 2: Select The Output Cell
Your XLOOKUP requires a particular cell to project its data in. So once you have organized your spreadsheet and have made room for the outcome, select the empty cell where you want your result to show up. This is where you’ll be applying the formula and complete your task.
Step 3: Feed In The Formula
Within the selected cell or at the formula bar, type in your XLOOKUP formula. Follow the format mentioned earlier in this article to properly implement it to your workflow.
First, place the =XLOOKUP part that’ll draw Excel toward the function you’re trying to apply. Then inside the () brackets, mention your target value – either the cell number or the value itself should suffice.
After that, insert the lookup array in the shape of cell ranges like B4:B8 or even you can go for absolute reference like $B$4:$B$8.
In the same manner, place the return array like D4:D8 or $D$4:$D$8. Once these basic information are settled with, move on to the next step to deal with the additional arguments.
Step 4: Place The Arguments
Now it’s time for you to tweak the optional arguments. Leaving the rest of the commands blank will give you default settings: #N/A error for data that’s not found, Excel will look for an exact match and the function will search the data set from first to last.
I’ve provided a few examples in the following section to help you understand the ways you can toy with these optional arguments in certain situations to draw out your data in the best possible way.
Step 5: Receive The Outcome
Once you’re satisfied with the formula implementation, hit Enter on your keyboard to let Excel do the search and come up with your desired outcome!
Different Samples Of XLOOKUP
In this section, I’ll demonstrate different uses of XLOOKUP function so that you can pick up some ideas on how you can implement it in your workflow and produce creative results!
1. The Basic:
Let’s start off with the basics. I’ll apply the XLOOKUP function without using any optional argument and show you how you can get started with your own data set.
First, I’ve picked out a sales record of XYOLogic employee’s that’s neatly organized in columns of employee names and their sales profits.
Then, I’ve organized a section where it asks: How much profit did the following employee earn in the last quarter? And on a cell right below I’ve mentioned the name of that employee.
What this does is, it allows me to feed the formula with the target data. The target employee is Jabed, his name is written in cell H7, the name list exists within the B5:B22 array and the information I’m trying to retrieve resides in E5:E22 array.
So the formula line stands as such:
=XLOOKUP(H7, B5:B22, E5:E22)
One hit on the Enter key and Excel will automatically find out how much profit Jabed made during that quarter. Easy, isn’t it?
2. Finding Nearest Value:
Finding the nearest value is basically looking for an approximate match. XLOOKUP has come a long way to aid us in finding the next value by allowing us to determine the deficit using the argument.
I have a data set of employee lists which are alphabetically sorted. My job is to locate two employees: one who is 60 or just crossed 60, and the other who’s closest to being 60.
Initially, I’ve set up my document in such a way where I have two questions asking both of these and the answer area makes room for the outcome.
Then, for the employee who has crossed 60, I opt for this command:
=XLOOKUP(60, E3:E21, B3:B21, “Not Found”, 1)
You’ll notice I’ve used two additional arguments, one being “Not Found” and the other is a 1. I’ll get to the Not Found part later. 1 here dictates I’m looking for a number in the match mode that sorts out the closest larger number to the one I provided, which turned out to be 64 year old David Barnes.
For the latter information on the employee right below 60, I use this command:
=XLOOKUP(60, E7:E25, B7:B25, “Not Found”, -1)
The -1 in the end applies match mode that looks for a lower number closest to 60. In this case, it’s 59 year old Eli Jones.
Now you know how you can tweak your approximate match mode to crunch out data you don’t have precise knowledge about!
3. Fixing The #N/A Error:
In the previous section, I mentioned the “Not Found” argument in the XLOOKUP formula. Here I’m going to talk about the ways you can use it to fix the #N/A error.
Suppose, you are searching for an exact match with XLOOKUP without using the optional commands. If your search doesn’t find a fitting result, it’ll the #N/A. The formula would look like this:
=XLOOKUP(35, E3:E21, B3:B21)
But if you add the not_found argument with a return value inside the “”, you can get that output if your target data doesn’t exist in the list. The complete formula would be:
=XLOOKUP(35, E3:E21, B3:B21, “No, there isn’t.”)
This way, you can create a neat-looking data set that’s free from errors and provides comprehensive output without using any additional commands and sticking to the basic XLOOKUP function.
4. Wildcard Match:
For VLOOKUP or HLOOKUP, the Wildcard match is a handy but to some extent confusing argument. With placing symbols like ?, * or ~ to make sense of your command, it’s easy to get lost and draw out inaccurate results.
But with XLOOKUP, it has become a straightforward argument that’s dictated by a simple digit of 2 in the match_mode argument.
Say, you have a large data sheet and you know an employee only by her surname. But if the name column contains full names, you won’t be able to apply any lookup functions to find them out.
This is where the Wildcard match comes in. Add an asterisk before the surname to tell Excel that there’s something before that part. I’m looking for Miss Sanders’ department, and this is how I’ll place my formula:
=XLOOKUP(“*Sanders”, B3:B21, C3:C21, “Not Found”, 2)
This will search out her department without you having to learn her first name. It saves a lot of time and effort with only a symbol and a match_mode number.
You can also do the same with the first name. In such a case, instead of putting the asterisk before the name, you’ll have to place it right after and Excel will do the rest.
5. Working As INDEX+MATCH:
The way you can merge INDEX and MATCH functions together to find out specific data from a complex set without complicating things, XLOOKUP has the potential to achieve that with minimum fuss!
Suppose you’re working with a large data set and feel the need to find out the sales amount of a particular quarter about a specific employee.
With such precise tasks, XLOOKUP has all the flexibility for you. The formula line is this:
=XLOOKUP($I$4,$C$3:$F$3,XLOOKUP($I$3,$B$4:$B$8,$C$4:$F$8))
This double instance of the XLOOKUP application is in general called nesting. The primary portion of the formula dictates the period of the target employee.
The latter portion identifies the particular employee and the range where that data might exist. Upon hitting enter, it’ll find out the sales record from tons of data with only one function.
6. SUM Values With XLOOKUP:
When you are made to sum-up a portion of your data focusing on a target data, XLOOKUP can provide that by nesting with SUM function.
From my set of data, I have to calculate how much the employee Tumul earned during the 2nd and 3rd quarter of the year. If it’s a large data set, finding out where Tumul’s records are and calculating them out can be pretty strenuous.
So, this is how I nest up the formula:
=SUM(XLOOKUP(I4,B3:B7,D3:E7))
I4 cell represents the employee name, B3:B7 dictates where Excel can find that employee and D3:E7 is the Q2 & Q3 data array from which Excel will draw the numbers out.
After that, the SUM function will automatically add up that data and provide you with a cumulative output on your selected cell!
This way, you can nest functions like IF, AND, OR etc. to creatively navigate through your data set without having to go through all of them individually and generate results in a matter of minutes!
7. Getting More Than One Outcome:
Excel XLOOKUP is actually a multidimensional function that can find multiple columns of data and place them across the same number of cells.
For instance, I have a data set that contains employee records of different categories, organized under distinguished columns.
If I’m asked to present a particular employee’s records in more than one column, XLOOKUP has the capability to deliver that. The formula line is this:
=XLOOKUP($H$6, A2:A20,B2:E20)
I’ve used absolute reference for the target employee, you’ll still get the same result with relative reference. I primarily select the range where this date exists, and then the multiple column range that I wish to present.
Upon hitting Enter after typing out this formula, Excel will place the data in separate columns that show all the necessary information on that employee.
#VALUE! Error Fix For Excel XLOOKUP Multiple Column Output
Some of the users have complained that upon using this formula, they don’t get results spread across the columns, instead they receive a #VALUE! error.
To resolve this, instead of selecting only one cell, select the same number of adjacent cells matching the number of columns.
Then, type in the aforementioned formula in the formula bar and don’t press Enter, rather press Ctrl+Shift+Enter. It’ll sort your troubles out and show your output across the selected cells.
XLOOKUP Advantages
You might wonder why you’d pick XLOOKUP over the pre-existing Excel functions. Let me give you a number of reasons why XLOOKUP is the way to go about finding data with certain edge at different areas:
It Can Go In All Directions
XLOOKUP isn’t confined by the search direction as it can go both vertically and horizontally back and forth. You can search for your target data from the right-most column or the bottom row and move your target array toward the left or upward. It’ll still find you the accurate result.
It Can Output Multiple Data
It might come to you as a surprise but XLOOKUP can indeed deliver multiple data instead of just one. If you put your command in a certain way, you can get two, three or even more outputs using this function.
The Default Setting Provides An Exact Match
As the other lookup functions are set to provide an appropriate match by default and you need to manually add an argument to receive an exact match, XLOOKUP does it by default, without having to address any argument whatsoever.
It Can Return An Entire Row Or Column
This basically echoes the multiple data output. XLOOKUP can go to the extent of delivering an entire row or column as the result if you place your formula arguments in such a way.
Converges Features Of Multiple Functions Into One
XLOOKUP is so advanced that it basically performs the tasks of HLOOKUP, VLOOKUP, LOOKUP and even more within one function. It also overcomes their limitations in more than one way.
If you go through the differences between XLOOKUP and VLOOKUP, you’ll realize the advantages even further with practical examples.
Tips For XLOOKUP
Let me offer you some important tips for Excel XLOOKUP so that you can avoid making errors and produce an efficient workflow with utmost accuracy.
- You can merge XLOOKUP with different formulas to isolate not only the target data, but also sum-up that data area for a convenient result generation.
- Always keep your data sorted. I’ve mentioned it earlier, and I’m mentioning it again because it renders your result accurate.
- If you’re on a newer version of MS Office, it’s better to implement XLOOKUP than settling for older VLOOKUP, HLOOKUP or LOOKUP functions.
- You have to keep all the workbooks open when you’re working with multiple data sets and planting results from them. Otherwise it’ll give you a reference error like #REF!
- Make sure your array selection is accurate and they are compatible with the arguments as otherwise you’ll receive errors like #VALUE!
If you keep these in mind, you’ll find yourself making less errors and identify the reasons for falsified outcomes!
Thoughts
As this marks the end-point of this article, I hope I was thorough in my breakdown of the Excel XLOOKUP function as well as the formula line arguments.
It’s really a great tool that allows diversity in your workflow and lets your creativity break past the confinements of the software.
Since it’s a very new addition to Excel, it’s made to cater to our recent needs as Microsoft has implanted upgrades from taking numerous feedbacks of frequent users.
Once you’re up to speed and find yourself familiarized with all the nooks & corners of XLOOKUP, it’ll be your time to shine in the office.
Surprise all of your employees with your newfound skill and spend that extra time XLOOKUP will save you by sipping coffee and listening to your favorite tune!
Similar Post: