XLOOKUP?! Welcome X Functions.

• Author: Lenning, Jeff

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. Eor 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.

Moving On

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)

-XLOOKL Pdookup_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...