XLOOKUP Function in Excel

XLOOKUP Function Overview

XLOOKUP Function in Excel is an extremely powerful lookup tool recently introduced by Microsoft. This single function allows you to perform both a vertical (VLOOKUP) and horizontal (HLOOKUP) lookup. Unlike VLOOKUP and HLOOKUP, XLOOKUP performs an EXACT lookup by default.

When VLOOKUP was introduced 34 years ago, it came with several limitations that XLOOKUP has just fixed. These are outlined below.

Please note, Microsoft is in the process of rolling this update. Most users should have this update pushed in the next couple months. To check for the update within Excel, Select File – Account – Update Options.

• Syntax
• Video Demo
• XLOOKUP Functionality vs VLOOKUP Limitations

Syntax

The default syntax for XLOOKUP has just 3 arguments. However, it does allow for a 4th and 5th arguments.

=XLOOKUP (lookup,lookup_array,return_array,[match_mode],[search_mode],[not found]) 

Arguments

• lookup – The lookup value.
• lookup_array – Array or range to search.
• return_array – Array or range to return.
• match_mode – [optional]
  • 0 = exact match is the default
  • -1 = exact match or next smallest
  • 1 = exact match or next larger
  • 2 = wildcard match.
• search_mode – [optional]
  • 1 = search from first-to-last is the default
  • -1 = search from last-to-first
  • 2 = binary search ascending order
  • -2 = binary search descending order
• not found – [optional] Value to return if no match found.

XLOOKUP Demonstration Video

Here is a video from Leila Gharani, an Excel MVP, reviewing how XLookup just change the Excel landscape.

XLOOKUP Function Video Demo from Microsoft

As the update is pushed, we will be updating this post with more examples.

XLOOKUP Functionality vs VLOOKUP Limitations

What makes the XLOOKUP function so great?

XLOOKUP can look left!

• That’s right. With VLOOKUP, Excel only searches the first column then returns a value to the right. This is extremely annoying if you have large data sets. This requires most users to rearrange their data in order to use a VLOOKUP. Not anymore, this annoying feature has been fixed.

VLOOKUP defaults to “Approximate” Match

• The default in Excel is TRUE, which is an Approximate Match. This leads many users to obtaining the incorrect result. For an EXACT match, the user must us FALSE as the fourth argument. XLOOKUP defaults to an EXACT match.

XLOOKUP is dynamic

• Using VLOOKUP, if you inserted or deleted a row, your VLOOKUP functions would break. With XLOOKUP, this is no longer the case.

Find next larger item

• VLOOKUP only finds the next smaller item using an Approximate match. XLOOKUP allows you to do both by using the 4th and 5th arguments.

Reference less cells

• When using a VLOOKUP, when selecting the array, you must select large sections of data that typically span over multiple columns. This obviously drives up computing power and slows down your computer. With XLOOKUP, you now can select the columns you want to search individually.

Why use VLOOKUP or HLOOKUP anymore?

Good question. Microsoft will continue to support both functions. However, I foresee their usage slowly decreasing over time. I have worked with several spreadsheets that are running hundreds of VLOOKUP’s. When opening these spreadsheets, it can take several minutes for the Excel to update. Forget sending those files to colleagues.

In my opinion, the XLOOKUP Function has been a long time coming. As data sets have grown as well as the need to analyze data, Microsoft has realized a need for a stronger lookup function. I’m excited to put this function through it’s paces. I’m already working on updating daily spreadsheets at work to incorporate this new functionality.