How To Use Excel’s XLOOKUP Function [Guide 2022]
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 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 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.
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
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.
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 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. 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 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 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.
Replacing 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.
Below shows how XLOOKUP can perform the very same lookup.
Replacing INDEX/MATCH
Example 1 – INDEX/MATCH
INDEX/MATCH formula has 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 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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.