The XLOOKUP function has been just recently announced as a new addition to the Excel functions base. With it came also other less known functions such as the XMATCH etc. This seems to end a decades old battle between fans of VLOOKUP vs INDEX MATCH. Even news sites caught this announcement as somewhat special. In this post we will understand the differences between both – at least based on the limited information we know.
To understand the differences of XLOOKUP vs VLOOKUP it is best we compare the definitions of both formulas and explain using examples.
Starting with a reminder of the VLOOKUP function. The Excel VLOOKUP function allows you to find a row in a column that matches a certain value and returns a value from another corresponding column in that row. It works like a phone book e.g. find the name of a person and return his/her phone number.
- The lookup_value needed to be in the first column of the table_array. Otherwise you would need to reorganize the columns
- The col_index_num was not a reference on the number of the column in the table_array where the returned value can be found. Hence adding a column in between would cause the whole formula to return values from the incorrect column
- The range_lookup was most of the time a useless argument, usually being set to FALSE (exact match)
- No longer do you need to worry about the lookup column being first in your table
- You don’t have to worry about breaking any lookup formulas by adding or deleting columns from your table
- You save time – as the basic version of XLOOKUP assumes you are looking for exact matches
We can also explore the full definition for the XLOOKUP function:
XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
We already described the first 3 parameters however we also have 2 remaining:
- match_mode – the type of match you want to make. What is interesting is the mode 2 allowing you to use ? and * wildcards to replace a single or any number of characters in a lookup operation
- search_mode – type and direction of search. Useful when there is a particular sorting order of data.
I can’t wait to try this function out on actual data.
XLOOKUP vs VLOOKUP – Conclusions
When will XLOOKUP become available to Excel users? As per this article:
XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We’ll continue to optimize it over the next several months. When XLOOKUP is ready, we’ll release it to all Office Insiders, and Office 365 subscribers.
Although XLOOKUP is still only recently announced I already see it as a final resolution to the problem of lookup operations in Excel. I am frankly much surprised it took Microsoft so long to introduce this function. If you are interested in how XLOOKUP will compare in performance to VLOOKUP stay tuned.