Microsoft recently announced the upcoming release of a new function called XLOOKUP. This function will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.
Although it may have taken many years to get to this point, Excel users now have a new function that is more user-friendly, more flexible and avoids some frustrating VLOOKUP mistakes.
XLOOKUP is coming...
So let’s see how the XLOOKUP function works, how to use it in your Excel formulas and how it compares with the previous solutions.
TABLE OF CONTENT
Reminder: the Excel VLOOKUP function
Note: we mention VLOOKUP all the time in this post, but actually everything about VLOOKUP also applies similarly to HLOOKUP.
How to use VLOOKUP
VLOOKUP is one of the few Excel functions most widely used around the world.
What is it for? Say on one hand you have a list of IDs (for instance, a social security number), and on another hand you have a correspondance table, with all IDs in one column and the name of each corresponding person in the second column.
Now what you need is to identify in your 1st list, who the people are based on their IDs. Technically, you want to add, next to each ID from your 1st list, the name corresponding to these IDs based on the correspondance you have in the 2nd table.
So how do you achieve that? Hint: the answer is NOT to spend the next couple of months checking each ID one by one. So what you would typically do is use the power of the VLOOKUP function in Excel.
This is how it looks:
=VLOOKUP(lookup_value, table_array, column_index, [match_mode])
lookup_value: this is what you want to look up, here it's your 1st list of IDs.
table_array: this is where you want to look for it, here it's the 2nd list which is a table with the correspondance between all IDs and name.
column_index: this is the number of the column, from the table_array, which contains the data you want in output. Here you want the content of the 2nd column (the names)
[match_mode]: 99% of the time you will want to type 0 to specificy that you want an output only if the VLOOKUP function finds an exact match in the table. # N/A will be returned if no match is found. When it's 0 (or FALSE) the function returns a value only when the lookup_value is found in the table_array. But when it's 1 (or TRUE), it returns the next closest value found even if the lookup_value is not found. Sometimes it's useful, but most of the time it's a dangerous mistake giving you wrong results!
An example is always better that some neardy gibberish, so check this one:
The problem(s) with VLOOKUP
So, what is wrong with VLOOKUP?
With VLOOKUP there is a bit of the good, the bad and the ugly.
The good is that once you understand it, you realize that it performs a complex role that is actually needed all the time. When you work a lot with Excel, you are using this function A LOT.
The bad is that VLOOLUP is really slow to run. Most of the time, you won't even notice. But if you start running it on thousands of rows, you will notice that it takes a few seconds to calculate. And if you reach the tens or hundreds of thousands, you may very well spend a few minutes looking for Excel calculating the results. Yes, that's bad.
Hint: check our post about the DOUBLE VLOOKUP trick to make VLOOKUP run a hundred times faster!
The ugly is that VLOOKUP is really NOT practical nor flexible. For 3 reasons:
It only works from left to right. So in our example, if the IDs were in a column right of the names, it wouldn't work. Damn.
The column with the desired result is designated by an index. If you want VLOOKUP to return data from the 2nd column, you need to type 2. But what if at some point you insert another column? Well, the index will still be 2, and your function will not return the expected result anymore. Damn again.
Microsoft made a big mistake with VLOOKUP. The match_mode argument is optionnal with VLOOKUP... but for some reason, they decided that it would be TRUE by default, while people only use it as FALSE. So although this argument is theoretically optional, in practice it's mandatory because you need to specify FALSE each time. Yes, that's ugly.
A partial improvement: combining the INDEX and MATCH functions
One of the popular improvements to the VLOOKUP limitations is to combine 2 Excel functions, INDEX and MATCH.
This is the syntax:
=INDEX(return_array, MATCH(lookup_value, lookup_array, [match_mode]))
The main point of using the INDEX/MATCH combination, is that it replaces the column_index with a reference. This is way more flexible:
1) The lookup column doesn't need to be on the left of the table
2) You can insert and delete columns without breaking the formula
3) It works both vertically and horizontally, while VLOOKUP needs to be switched to HLOOKUP for horizontal data searches
Also, the INDEX/MATCH combination runs faster than VLOOKUP, which can make a difference on large sets of data.
However the Excel INDEX MATCH formula is not perfect. The main drawback is that it is longer and more complex. For typing formulas quickly, or if your file needs to be read by not-as-great-as-you Excel users, you may prefer going for VLOOKUP.
And now... this is where XLOOKUP comes in.
How to use XLOOKUP, the future of lookup functions in Excel
How to use the XLOOKUP function in Excel
XLOOKUP was released by Microsoft 365 in 2019 and is meant as the replacement for VLOOKUP, HLOOKUP, INDEX/MATCH functions.
The XLOOKUP syntax is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Like VLOOKUP or INDEX/MATCH, there are 3 mandatory arguments. The last 3, which are in square brackets, are optional arguments:
lookup_value: this is the value you are searching for
lookup_array: this is the range where you are searching for it
return_array: this is the range with the result you want
[if_not_found]: instead of # N/A, you can specify what should be returned in case no match is found.
[match_mode]: like with VLOOKUP or INDEX/MATCH, use 0 for an exact match (# N/A will be returned if no match is found). But but but... exact match is the default value! So basically most of the time you won't need to use this argument! This is a big improvement compared to VLOOKUP and INDEX/MATCH that made approximate match the default behavior. With XLOOKUP, you can also use -1 to return the next smaller value if no match is found, or 1 to return the next larger value if no match is found. It is also possible to use 2, which is a wildcard match with special meaning for characters "*", "?" and "~".
[search_mode]: Use 1 to search starting with the first item, and -1 to search starting from the last item. You can also use 2 to search a lookup_array sorted in ascending order and -2 to search a lookup_array sorted in descending order (see about running binary searches in sorted arrays in our post about the DOUBLE VLOOKUP trick). The results returned will be invalid if the array is not sorted appropriately.
Example 1: basic XLOOKUP
Let's start focusing on the mandatory arguments. Here is how XLOOKUP looks in its simplest form:
As you can see above, XLOOKUP is used in G2 to display a country telephone prefix based on the user input in F2.
See how easy it is?
VLOOKUP is (almost) dead
With XLOOKUP we are getting the best of 2 worlds: like VLOOKUP it is very simple to type and remember, and like INDEX/MATCH the lookup_array is a reference that makes it more flexible. And icing on the cake, you don't need to specify the match_mode to get an exact match.
Now let's see how you can use those scary optional arguments to make some awesome formulas.
Example 2: using the if_not_found argument
Below, the if_not_found argument is used to add a custom error message:
That's much clearer than getting # N/A results that people are not always sure how to interprete.
In this example, the if_not_found argument is text. But you can also return a value or a range. In many cases, it saves you the effort of combining VLOOKUP with IF, IFERROR or other formulas to handle the absence of match.
Example 3: using if_not_found, match_mode and search_mode arguments
Below, all 3 optional arguments are used:
The if_not_found argument is used, returning a value of 0 instead of # N/A if no match is found.
The match_mode argument is used, set to 1, so it’s looking for an exact match or the next larger item.
The search_mode argument is also used, set to 1, so searching starting from the first item down to the last.
Example 4: returning date from multiple columns
In the below example, XLOOKUP is being used to search for and display an employee’s information based on their ID.
XLOOKUP is displaying both the Employee Name in C2 and the Department in D2, with one single formula.
This is because XLOOKUP is able to return arrays with multiple items. This is very powerful, and a great time-saver if you need to get data from many columns.
Pay attention to the 3rd argument (the return_array argument). You would typically expect it to be C5:C14, but instead it's C5:D14, meaning that the results will come from both the C and D columns!
Other advanced features of XLOOKUP
Wildcards
The fourth argument in XLOOKUP is the optional [match_mode]. This one has four options.
The first three (0, 1 or -1) are similar to MATCH function and allow to perform an exact match, to get the next smallest value in case no match is found, or to get the next larger value in case no match is found. Nothing new here.
The fourth option is the wildcard character. Use 2 as [match_mode], and it allows you to leverage wildcard characters to run partial match lookups. This can be extremely helpful.
The asterisk (*) represents any number of characters, while a question mark (?) represents any single character.
Let's take an example. Imagine you want to search for a specific last name, in a list of full names. You will run a partial search, as shown below:
Here the first argument ("*"&A4) could be read as "anything followed by the value in A4". Typically, you could also use "*"&A4&"*" to say "anything that contains the value in A4. This means that thanks to wildcards, you can use XLOOKUP to search for values that contain the lookup_value, and not just values that equal it.
Now another example: suppose you want to search for a name, but you are unsure if it should be spelt with an "i" or an "y". You can use the question mark wildcard to specifiy that this character could take any value, like in the example below:
Wildcards are useful in general, and not just for XLOOKUP. You can learn more about them here.
Remember that you need to use 2 as [match_mode] to use the wildcard characters in XLOOKUP.
Reverse Order Search
Using the [search_mode] optional argument, you can run searches in reverse order. The default is 1 to search in first-to-last order, but you can use -1 to search from last-to-first. The search will run in reverse order, either bottom to top or right to left.
This can be useful if you are looking for the last ocurrence of the lookup_value.
XLOOKUP Vs VLOOKUP Vs INDEX/MATCH
Let's recap how XLOOKUP outperforms VLOOKUP and INDEX/MATCH:
✅It is the simplest function, with only 3 arguments needed in most cases because the default match_mode is 0 (exact match).
✅It's a single function, unlike INDEX/MATCH, so it's faster to type.
✅It works both vertically and horizontally, for data organized in rows or columns (unlike the VLOOKUP formula and its alter ego HLOOKUP).
✅It doesn't require the lookup values to be on the left (unlike VLOOKUP).
✅It can return custom results instead of # N/A when no match is found, without needing to combine additional functions.
✅It can return an array, and not just a single value.
✅It can do binary searches to calculate faster on sorted data when [search_mode] is -2 or 2.
✅It can run partial searches, leveraging wildcard characters when [match_mode] is 2.
✅It can run reverse searches when [search_mode] is -1.
Well, that is definitely a LOT of reasons to start learning and using XLOOKUP!
However, using XLOOKUP implies some drawbacks that must be kept in mind.
❌With all its issues, the column_index from VLOOKUP had the great merit of requiring only to type 1 character, instead of selecting the entire return_array.
❌This array must have the same size as the lookup_array or you will get an error.
❌Also, you should use absolute references before dragging the formula down or risk getting wrong results.
❌Finally, XLOOKUP is not backwards compatible. So before you start happily emailing spreadsheets with XLOOKUP all around, make sure the recipients will have an Excel version that reads it.
If you are an heavy user of Excel, you will most certainly like the Power-user add-in. Get dozens of new advanced features that will make you the boss of Excel!