How To Use Excel's XLOOKUP Function [Guide 2024] (2024)

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 can search both backward/forwards and vertically/horizontally, all with streamlined inputs. The understanding of this new function will truly be the next measuring stick (that VLOOKUP has served) to quickly assess employee Excel skills across the globe.

While the 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 its capabilities.

Here’s a look at XLOOKUP in action!

XLOOKUP Version Availability

You may be wondering if you have the ability to use XLOOKUP in your version of Excel. XLOOKUP has been released into the following versions of Excel:

• Excel Online
• Microsoft 365
• Excel 2021 or later

If you are unsure of which version of Excel you are using, you can also determine if you have the XLOOKUP function available by trying to type the function’s name in the Formula Bar. If the XLOOKUP name appears in the tooltip, then you know you have access to it!

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

1. Lookup_Value - This single-cell input is the value you are trying to look for using XLOOKUP

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

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

4. If_Not_Found (Optional) - You can use this input to control the return value if the lookup value cannot be found (which normally produces a #N/A error). If you want to output a text value if an error occurs, remember to place “ “ around your text.

5. 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 anyway).

• [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

6. 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 (sort in ascending order)
• [-2] - Binary search (sort in 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 look up 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 is 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 to be 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 trying 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.

XLOOKUP Examples

XLOOKUP Replaces VLOOKUP

Let’s look at a basic use for a VLOOKUP function and see how 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 employee’s 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 needs to be referenced. There are no static inputs that would need to be changed if we were dragging this formula across multiple columns.

XLOOKUP Replaces HLOOKUP

HLOOKUP is used very prominently with data that utilizes dates. This is because spreadsheets with dates are typically displayed across the spreadsheet.

In the following example, the HLOOKUP function is 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 to adjust the formula.

The below image shows how XLOOKUP can perform the very same lookup.

XLOOKUP Replaces INDEX/MATCH

Example 1 - INDEX/MATCH

INDEX/MATCH formula has been long preferred for its ability to perform right-to-left lookups (which VLOOKUP is incapable of doing). The below image 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 horizontally and vertically at the same time.

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

So how can we replicate this capability with XLOOKUP? The short 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 its answer. I wrote a more in-depth article on the mechanics of a Double XLOOKUP, but essentially 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:

I Hope This Helped!

Hopefully, I was able to explain how you can use the new XLOOKUP function to replace a bunch of the common lookup methods in Excel. If you have any questions or suggestions on how to improve this article, please let me know in the comments section below.