Microsoft recently announced that XLOOKUP would be the new replacement for VLOOKUP. Although it may have taken many years to get to this point, Excel users now have a new function that is much more user-friendly and avoids some frustrating VLOOKUP mistakes. There are still some drawbacks with the new XLOOKUP, so let’s dive right into the function now.
What is XLOOKUP?
This is a function that will find a value in a row (column) and return a value from a column (row) that’s adjacent if a match is found. It operates under the same principle as VLOOKUP but it has some new features.
The XLOOKUP signature (argument) for Excel is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
It might look a bit intimidating at first glance, but you really only need to complete the first three values. The last two, which are in square brackets, are optional arguments.
In fact, having only 3 integral arguments, XLOOKUP is far less intimidating to learn and come to terms with than the other LOOKUP functions.
How do XLOOKUP and VLOOKUP Work?
With VLOOKUP, merely typing the function takes some time since it has 4 mandatory arguments. On top of this, the output may still not be correct because if you insert or remove columns, the 3rd argument’s index isn’t automatically updated. As well as this, the 4th argument can give output values that don’t match the lookup value if you’re using TRUE mode. Another drawback is, VLOOKUP always takes a long time to calculate. It is not unheard of for a VLOOKUP calculation to take over half an hour if many rows are involved.
However, VLOOKUP’s drawbacks have their workarounds. For example, if you use INDEX and MATCH, the index used in the 3rd argument will then automatically update. As well, VLOOKUP’s calculation speed increases greatly if you use the Double VLOOKUP Trick – we have covered this in another post.
In the above example, XLOOKUP is used in G2 to display a country code based on the user input in F2.
With XLOOKUP, there are 3 main arguments – the lookup_value, the lookup_array, and the return_array. The first argument, lookup_value, is just for the value you’re searching for, but the lookup_array allows you to set the range you’re searching. The last important argument, return_array, lets you choose the range to return. But as well as these, the optional arguments [if_not_found], [match_mode] and [search_mode] allow you to set your own error message if no valid match is found, rather than showing #N/A, and allow you to specify the match type and search mode – with [match_mode], 0 is exact match with #N/A returned if none is found, -1 is exact match which returns the next smaller item if none is found, 1 is exact match which returns the next larger item if none is found and 2 is a wildcard match with special meaning for * ? and ~. Whereas with [search_mode], 1 searches beginning at the first item, -1 searches beginning at the last item, 2 searches a lookup_array sorted in ascending order and -2 searches a lookup_array sorted in descending order. The results returned will be invalid if the array is not sorted appropriately.
In the above example, XLOOKUP is being used to search for and display an employee’s information based on their ID. XLOOKUP can display all the employee’s data because of its ability to return arrays with multiple items.
Above, the if_not_found argument is used, to add a custom error message.
Above, 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.
Here is VLOOKUP in comparison, with the three mandatory lookup_value, Table_array, and col_index_num arguments.
Here is HLOOKUP as well, with lookup_value, table_array, row_index_num and range_lookup.
XLOOKUP vs. VLOOKUP (and HLOOKUP)
There are four critical differences between the two functions. The first is that there are two different arguments for the lookup and the return arrays. This is the same way that INDEX MATCH functions. VLOOKUP only has a single table array that has the column to look in as well as the return column. This can create problems in our formulas when inserting or deleting columns in the range. XLOOKUP doesn’t have that problem because the lookup and return ranges are split in different values.
Another main difference is the default to the exact match. VLOOKUP has to be an exact match so the last argument always has to be FALSE or zero. But with XLOOKUP, you only have to note three arguments to lookup an exact match because that’s the default mode. XLOOKUP allows the user to do approximate matches or use wildcard characters.
The third main difference is that XLOOKUP can run horizontal lookups without using a different function like HLOOKUP. Instead, specify single rows instead of columns for return and lookup arrays. Last but not least, you can use XLOOKUP to return a value to the left or above of the lookup range, which wasn’t possible previously with VLOOKUP.
Other XLOOKUP Features
XLOOKUP at first glance seems very close to the INDEX MATCH function. The main difference is that the lookup can be done through the singular function of XLOOKUP instead of two functions in INDEX MATCH. Microsoft has gone beyond INDEX MATCH though, to add extra useful features to XLOOKUP.
1. Wildcards
The fourth argument in XLOOKUP is the optional [match_mode]. This one has four options, the first three of which are similar to MATCH function (exact match, less than, or greater than). The fourth option is the wildcard character. This helps you run partial match lookups, which is extremely helpful. XLOOKUP lets you use the asterisk (*) or question mark (?) character in the lookup value as a wildcard. The asterisk stands for any number of characters whereas the question mark represents any single character.
If you look for either of those things, you can put a tilde character ~ in front of the search character, then specify a 2 for the argument to turn on wildcard character match. This is excellent especially in tables where there may be misspelled names.
2. Reverse Order Search
You can also run a reverse-order search, which is an optional argument in fifth, the [search_mode]. The default is 1 to search in first-to-last order, but you can now specify -1 to search from last-to-first. The search will run in reverse order, either bottom to top or right to left. This is very useful for looking up the last time an item comes up in a list and finding a matching result, but there are many more use cases where this function will be a life-saver.
The Downsides
We spoke in-depth about the benefits of XLOOKUP and how it has major advantages, including the default to exact match, the three arguments required, its multi-direction functionality and one function instead of two, the wildcard characters, and reverse lookups. There are also drawbacks though which must be mentioned in this guide.
The first one is that new users might find the optional arguments to be overwhelming in the function. If the lookup and return arrays are not of the same length, you will get a #VALUE! error which can be problematic. Some users can also find it quite time-consuming to pick the two ranges with the click of a mouse, especially for large tables.
All that being said, there are some great new features that make this tool well worth it. It’s now only available to Office 365 subscribers. It’s unfortunately not backward compatible so you can only send your XLOOKUP files to people on Office 365 with that build supporting XLOOKUP function.
Beatrix Potter writes for Paper Fellows and Business Writing Service UK on matters of software development and business. She enjoys writing tech hacks. Beatrix also writes articles for Essay Assignment Help in her role as a tutor.
