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.

Update to XLOOKUP

Syntax

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

 =XLOOKUP(lookup_value,lookup_array,return_array) 
  • lookup_value: The value you are looking for.
  • lookup_array: Cell where you are looking.
  • return_array: Value you are returning.

XLOOKUP Function Video Demo from Microsoft

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

Demo video from Microsoft.com

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.

XLOOKUP Variations – 4th and 5th Arguments

  • match_mode – Set the type of match. 4 options
    • 0 – Exact Match (default)
    • -1 – Exact match or next smaller item
    • 1 – Exact match or next larger item
    • 2 – Wildcard character match
  • search_mode – Set the type and direction of your search
    • 1 – First-to-Last
    • -1 – Last-to-First
    • 2 – Binary search (ascending order)
    • -2 – Binary search(descending order)

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.