If you’re using Office 365, don’t miss out on the awesome new Excel function—XLOOKUP. That’s not a typo; XLOOKUP is a real thing! It’s designed to return a related value, just like VLOOKUP, but it has fewer limitations. You no longer need the data you’re looking for located in a specific number of columns. Check out this screenshot of the Insert Function dialog, which provides a description (and proof that XLOOKUP exists):
XLOOKUP Syntax
In its simplest form, the VLOOKUP function is:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
More clearly stated, this means what you want to look up, where you want to look for it, the column number in the range containing the value to return, and whether you want to find an approximate or exact match.
Now, let’s break down this formula even further, so you can understand the differences between VLOOKUP, how the Lookup_value is similar to VLOOKUP, and how it represents the value you are trying to find.
VLOOKUP vs XLOOKUP: What’s the Difference?
There are a number of differences between VLOOKUP and XLOOKUP. The Excel Match function, Lookup_array, represents a big change from VLOOKUP. Instead of defining the entire two-dimensional range, or table_array, you can define a single column, and specifically identify the lookup column range. This means that column order no longer matters. The lookup column can be anywhere in the table.
The value to match, Return_array, is another big difference from VLOOKUP. Instead of defining the return column by index number, col_index_num, you can select the return column range. This is huge, because it means if you insert a new worksheet column between the lookup and return columns, nothing will break.
The match_mode value is an optional argument, and defaults to 0 if omitted. This means that if you don’t specify it, it will perform an exact match. This is different than VLOOKUP, which defaults to a range lookup.
Additionally, XLOOKUP offers more match options shown below:
The wildcard character match is helpful when performing a partial match. To use this option, just add a wildcard character like asterisk (*) to the lookup value.
Another optional argument, search_mode, defaults to 1 when omitted. The values shown below replicate the behavior of VLOOKUP, when finding a match by starting at the top and working down. You can also search from the bottom up, now, which is a nice option.
Why Use XLOOKUP?
XLOOKUP is an amazing new Excel function that definitely has some major advantages over VLOOKUP. Take your financial analysis to the next level! Improve your workbooks by eliminating errors and using long, nested IF statements to verify multiple conditions. For all of you number geeks, enjoy the early Christmas present from Microsoft!
Catherine Filar
Catherine has more than 20 years of experience in data analysis, data mining, financial modeling, and accounting. When she’s not crunching numbers, she enjoys spending time with her family, and she often likes to say that nothing is impossible.
