Welcome X Functions – XLOOKUP

VLOOKUP is perhaps the most iconic function in Excel and users have come to adore it. When you search for a list of top Excel functions, VLOOKUP is there. And has been … for decades.

As great as VLOOKUP is, it has limitations. For example, column order matters. It was designed to look for a matching value in the first (left-most) column within the lookup range. Once found, it scans to the right (not left) to retrieve the related value. Also, it’s one of the few functions that can break when we insert a new worksheet column. Despite these limitations, Excel users love VLOOKUP.

Friends, it’s time to let go of VLOOKUP. You see, Microsoft has addressed these limitations in the next-gen lookup function — XLOOKUP. At the time of this writing, XLOOKUP is not universally available. Microsoft is not updating all prior versions of Excel to include this function. It’s initially being pushed out slowly to O365 subscribers and eventually will be available in future perpetual licenses as well. So, depending on when you read this, there is a good chance that your Excel version does not have XLOOKUP. Even though you may not have it right now, I do want to talk about it because it is something to look forward to.

Here’s a quick glance at the required function arguments for each function (there are additional, optional argument as well):

=VLOOKUP(lookup_value, table_array,col_index_num)
=XLOOKUP(lookup_value, lookup_array,return_array)

The first argument of VLOOKUP is “lookup value,” which is the value we’re trying to find. This stays the same in XLOOKUP.

A major difference between these functions is found in the next two arguments. The next two arguments are used to tell Excel where to look for a matching value, and once found, which column has the related value you want to return. For simplicity, let’s call the column that has the value you’re trying to find the “lookup column” and the column that has the value you want to return the “return column.”

With VLOOKUP, we communicate these two things by specifying the table_array and the col_index_num. In Figure 1, the table_array is the blue shaded range. VLOOKUP assumes the lookup column is the first column within that range. The return column is represented in the screenshot by the orange arrow. It’s defined with the col_index_num argument, which is a number — such as 3 — that represents the column’s position within the range.

With XLOOKUP, we communicate the lookup and return columns separately using range references. In Figure 2, the lookup range (lookup_array) is represented by the blue column and the return range (return_array) by the orange column.

Believe it or not, this seemingly simple change has a major impact. It means that the lookup column doesn’t need to be the first column in the table, that the return column can be in any position including left of the lookup column, and that inserting a worksheet column between the lookup and return columns won’t break things because Excel updates range references automatically.

There are some other improvements to look forward to, as well. For example, there are some optional arguments that provide new search options, including wildcard character match and searching last-to-first. Also, the MATCH function received an updated version as, well, XMATCH.

Even though you may not have access to these functions right now, you will … and remember, Excel rules!

If you’d like to learn more about XLOOKUP, I teach a free 20-minute webinar. More info:

• XLOOKUP Webinar Info