XLOOKUP, Improved Excel Lookup Function

The Excel function VLOOKUP, released in 1985, has been around for about 40 years. VLOOKUP is the third most used function in Excel (after SUM and AVERAGE). However, for a beginner, VLOOKUP is one of the most confusing functions to learn in Excel. Many decades ago, once I mastered VLOOKUP, I felt that I finally knew how to use Excel. In 2019, Microsoft released XLOOKUP as the successor to VLOOKUP (as well as HLOOKUP). XLOOKUP is a huge improvement over VLOOKUP. XLOOKUP is an easier way to find a value by searching a table or range with more options and fewer restrictions than VLOOKUP or HLOOKUP.

Advantages Of XLOOKUP Over VLOOKUP

Directional

Before XLOOKUP, if you wanted to perform a vertical lookup, you had to use VLOOKUP. If you wanted to perform a horizontal lookup, you had to use HLOOKUP. But XLOOKUP can search both vertically and horizontally.

In addition, XLOOKUP can return results from the left, right, above, or below. VLOOKUP could only return results from columns to the right of the lookup column.  HLOOKUP could only return results from the row below. XLOOKUP can search for a specific column/row that is left, right, above, or below.

VLOOKUP results must be from a column relative to the right of the lookup column.  In the above spreadsheet, if I searched on Author, VLOOKUP could only return Topic or Words.  If the desired result was in a column to the left (like the Pub Date or Title columns above), it was not possible to search with VLOOKUP without rearranging the table. 

Not Relative

VLOOKUP / HLOOKUP both use relative positioning (i.e. two columns to the right).  Adding or deleting a column/row could corrupt the entire function.

As we can see in this example, inserting a column breaks the VLOOKUP because the column reference number now points to the wrong column. But the XLOOKUP handled the insertion of a column just fine.

Matching / Searching

XLOOKUP has better matching. First, VLOOKUP defaults to a ‘near match’. This confuses many Excel beginners (and even experts). Most of the time, an exact match is what is desired. XLOOKUP defaults to an exact match. Second, XLOOKUP has better matching. XLOOKUP’s match mode parameter allows for exact, nearest lower, nearest higher, or wildcard matches.  XLOOKUP can find the next largest occurrence or last occurrence. Neither is possible with VLOOKUP / HLOOKUP. Finally, XLOOKUP allows for better searches by allowing for ascending or descending searches. With VLOOKUP, the spreadsheet table had to be sorted correctly.

XLOOKUP Syntax

The XLOOKUP function searches a range or an array and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest match.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value  The value to search for. (Required)
• lookup_array   The array or range to return. (Required)
• return_array    The array or range to return. (Required)

match_mode Specify the match type:

• 0              Exact match. If none found, return #N/A. This is the default
• -1            Exact match. If none found, return the next smaller item
• 1              Exact match. If none found, return the next larger item
• 2              A wildcard match where *, ?, and ~ have special meaning

search_mode     Specify the search mode to use:

• 1              Perform a search starting at the first item. This is the default
• -1            Perform a reverse search starting at the last item
• 2              Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned
• -2            Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned

Always Use XLOOKUP Instead Of VLOOKUP?

Hey John, since XLOOKUP is superior to VLOOKUP and HLOOKUP, should I always use XLOOKUP and never use VLOOKUP or HLOOKUP again? No, unfortunately, XLOOKUP is only available on Microsoft 365 or Excel version 2021 and newer. It is not available on Excel versions 2019 or earlier. Therefore, when sharing Excel files with colleagues who may be using an older version of Excel, you should continue to use the older functions.

Bottom Line

Microsoft’s addition of the new XLOOKUP function is a significant improvement over the older VLOOKUP and HLOOKUP functions. Unlike Excel’s other lookup functions, the XLOOKUP function can work vertically and horizontally with the same syntax. In addition, XLOOKUP can search up, down, left, and right. XLOOKUP does not depend on relative positioning. Finally, XLOOKUP has improved matching and searching functionality over the older lookup functions. However, XLOOKUP is only available in Microsoft 365 and Excel 2021 or newer and should not be used when sharing files with colleagues using older versions of Excel.

—