The Complete Guide To Excel's XLOOKUP Function

Microsoft Excel XLOOKUP Function Guide

On August 28th, 2019, Microsoft unveiled a new Excel function with the intent of making 3 beloved and rivaled functions obsolete. The targets were:

  • VLOOKUP

  • HLOOKUP

  • INDEX/MATCH

This new function called XLOOKUP has the ability to search both backwards/forwards and vertically/horizontally, all with streamlined inputs. The understanding of this new function will truly be the next measuring-stick VLOOKUP has served to quickly assess Excel skills across the globe. While this XLOOKUP won’t be mainstream for another year or so, it will be to your benefit to understand the capabilities of this function so you can start utilizing it once you have access to it’s capabilities.

AVAILABILITY: Currently this function has only been released to the Insider Faster versions of Excel. The function will only be available on Excel 365 once released to the public (not available on Excel 2019).

Here’s a look at XLOOKUP in action!

Excel Xlookup Function Spreadsheet Example

How To Use XLOOKUP

From the screenshot example above, you should feel very comfortable with XLOOKUP’s inputs as they are very similar in format to VLOOKUP and HLOOKUP. You will still reference a lookup value, however, instead of specifying an entire data set range, you can simply specify the ID column and the return value column (similar to a SUMIF function). This new input formats lends much more flexibility to the lookup function and note that you now only have 3 required inputs as the Match Mode input is now optional.

Excel Xlookup Formula Breakdown

Lookup_Value - This single cell input is the value you are trying to lookup using XLOOKUP

Lookup_Array - This range input provides an area for XLOOKUP to search for the Lookup_Value

Return_Array - This range input provides an area for XLOOKUP to return a value associated with the Lookup_Value

Match_Mode (Optional) - With this optional input, there are four options you can choose from to determine how the XLOOKUP will search through the lookup array range you provide. Unlike VLOOKUP, XLOOKUP defaults to an exact match if you do not specify a value for this input (which is what most users use anyways).

  • [0] - Exact match (Default)

  • [-1] - Exact match or next smaller item (Vlookup’s approximate match)

  • [1] - Exact match or next larger item

  • [2] - Wildcard character match

Search_Mode (Optional) - If you need to search in a particular order, there are a variety of options to apply.

  • [1] - Search first-to-last (Default)

  • [-1] - Search last-to-first

  • [2] - Binary search (sorted ascending order)

  • [-2] - Binary search (sort descending order)


Benefits: XLOOKUP vs. VLOOKUP

Exact Match Default

How many times have you had to fill out a VLOOKUP function and type either a FALSE or 0 into the last input to ensure you lookup an exact match? This annoyance is now gone as XLOOKUP defaults to an exact match!

No Disruptions from inserting rows/columns

Since XLOOKUP inherently uses a range to point to the area where the result resides rather than a static numerical reference, there is no risk of ruining your lookup formula when columns are inserted into the spreadsheet. I know everyone reading this has had this very situation happen to them at least once while using a VLOOKUP and it definitely not fun discovering your formulas are either broken or returning the wrong data!

Search Backwards

One of the largest pitfalls to VLOOKUP was the ID column had to be the most left column in your data set. XLOOKUP can search both forwards and backwards, so you no longer need to worry about moving your ID column around your spreadsheet.

Horizontal & Vertical Lookups

XLOOKUP has the flexibility to perform both horizontal and vertical lookups, essentially warranting VLOOKUP and HLOOKUP no longer needed. To seasoned Excel users this might not be a big deal, but many users who first master VLOOKUP typically get confused when try to understand how HLOOKUP works (even though conceptually it acts in a very similar way). XLOOKUP will be much more approachable for beginners since the lookup is performed based on the direction of the search range provided, not which function you are using.

Returns A Range Instead Of A Value

An extremely neat feature about XLOOKUP is it does not return value it returns the cell reference of what it looked up. This means you could theoretically sum a range between two XLOOKUPs.

Example: = SUM( XLOOKUP():XLOOKUP() )

It also means XLOOKUP can return multiple cells within a single lookup. This technique is utilized in the handy Double XLOOKUP which can replace the ever-so-popular Index/Match/Match formula.


XLOOKUP Examples

Replacing VLOOKUP

Let’s look at a a basic use for a VLOOKUP function and see have it differs from the new XLOOKUP function. In the example dataset we will use an employee name/city data table (Range H2:J10) to bring in the employees name to our sales commission table (Range B2:F10). We’ll look at using the VLOOKUP function first.

Notice with the VLOOKUP there are four inputs needed, the whole data table needs to be referenced, and we have a static reference to the column we wish to search in/return from.

Now let’s look at the same example but utilizing the XLOOKUP function instead.

With XLOOKUP, we only need to enter three inputs, only the data we are utilizing is being referenced, and there are no static inputs that would need to be changed if we were dragging this formula across multiple columns.

Replacing HLOOKUP

HLOOKUP is used very prominently with data that utilizes dates. This is because spreadsheets with dates typically read across the spreadsheet. In the following example, the HLOOKUP function is being used to pull the cost amount associated with the company’s first quarter. If the user decided to change “Q1” to “Q2”, the HLOOKUP would automatically pull in the second quarter without any need for adjusting the formula.

Below shows how XLOOKUP can perform the very same lookup.

Replacing INDEX/MATCH

Example 1 - INDEX/MATCH

INDEX/MATCH formula have been long preferred for their ability to perform right to left lookups (which VLOOKUP is incapable of doing). Below shows an example where the Employee IDs are stored at the end of the lookup table (Range H:J). The below example shows how an INDEX/MATCH formula can easily perform a lookup based on Employee ID no matter where that ID column is located on the spreadsheet.

Since XLOOKUP isolates the “Lookup” column and the “Return” columns, there are no restrictions on where your ID column is located as well. Let’s take a look at how XLOOKUP can perform the exact same lookup as our previous INDEX/MATCH example, but with a much more streamlined approach.


Example 2 - INDEX/MATCH/MATCH

Replacing an INDEX/MATCH is nice, but what about the ultra-dynamic INDEX/MATCH/MATCH? This combination of functions allows you to dynamically look both horizontal and vertical at the same time.

Here is an example where a single Index/Match/Match can be used to both pull in the Name and City column data into our table.

So how can we replicate this capability with XLOOKUP? The sort answer is you can’t…..with a single XLOOKUP. Let me introduce you to the Double XLOOKUP!

This method is a little tricky to grasp at first, but it all relies on XLOOKUP’s ability to return a Range not a Value as it’s answer. I will write a more in-depth article on the mechanics of this function combination, but essential we will be using a second XLOOKUP function to return the cell range reference that is used for the 3rd input in the first XLOOKUP function. Sounds fun, right?

Here’s how it would look in practice:



What Do You Think?

I know it’s a big tease to provide you with all this information prior to it actually being available in any of the production versions of Excel (at the time of writing this at least), but I would love to hear your thoughts on this function and if you think it as the potential to be the sole lookup function needed in Excel. Let me know in the comments section below!