One of the more exciting software features to be released in Excel is the XLOOKUP function. Just looking at the name, you might think I did a typo and meant vlookup. No, this is an entirely new spreadsheet function that makes looking up data easier with fewer limitations on your lookup column.
In fact, Microsoft recommends that you use it over VLOOKUP or HLOOKUP. The catch is that this lookup function is not available to all Excel users.
Where Can You Find the Function?
To start, this new lookup function is only available with Microsoft Office 365. If you use one of the non-subscription versions of Microsoft Office or Excel such as Excel 2019, you’re out of luck.
For the rest of Office 365 users, it’s being released in phases. The first group of users to get this feature belong to the Office 365 Insiders Program which is free. This is a “fast channel” program that offers more frequent software updates, but the application may have more bugs. Even after you join, it may take a little time before you can see the new XLOOKUP feature. Just be patient.
To check your availability, go to your Formulas tab and click Insert Function. The Insert Function dialog will open with all your functions. XLOOKUP should show under the Lookup & Reference category between VLOOKUP and another new function called VMATCH.
Why is XLOOKUP a Better Lookup?
The question on most peoples’ minds is what’s better? After all, who wants to learn something that doesn’t add value for Excel users. Here’s a shortlist I can see from playing around with various lookup scenarios:
- This function can work either vertically or horizontally. This is in contrast to VLOOKUP and HLOOKUP which are mostly locked to their orientation.
- You’re not dependent on a sorted list.
- You don’t have to worry about the function breaking if you add or remove columns.
- It defaults to exact match. If this is your preferred match type, then it’s one less argument you have to state.
- You’re not dependent on the first column.
- Your return array can be to the left of your lookup column.
There are more advantages such as using wildcards, different search modes, and match modes like approximate match. However, those are not covered in this introductory tutorial.
About This Example & Formula
For this XLOOKUP tutorial, I’ll be using a simple Excel workbook with 2 sheets. You can download it from the Additional Resources section.
My goal is to populate Column C with the correct Periodic Table number that will be returned from Column H.
By default, XLOOKUP will perform an exact match mode on my stated lookup value which is the element name. To do this, I’ll search off the common field “Name”, which appears in both ranges.
I’ve also added a second tab to the workbook called “Horizontal”. It contains the same information but the data is laid out differently so you can see the function’s additional versatility. This worksheet also has the formula added.
You’ll also note that I’m adding the formula using Insert Function and the Function Arguments dialog. I find this method easier when you’re learning a new function. But, there is nothing to prevent people power users from using the formula bar and entering in a string like:
Breaking Down the XLOOKUP Function
The function has 5 arguments, but the last two are optional. These optional arguments are for “Match Mode” and “Search Mode”. Excel notes this by making the mandatory arguments in a bold typeface.
[A] The Lookup_value represents the cell Excel will use to find a corresponding value from the Lookup_array. In this screenshot, we’ll be looking for “Californium” from cell B2. It is also noted in quotes to the right of the textbox.
[B] The Lookup_array represents the cell range a matching Lookup_value can be found. In this example, we’re telling the application to lookup Californium from cell B2 and find the same value in column I. I added the $ signs to make an absolute range.
[C] The Return_array represents where our answers can be found. We’re telling Excel that once it finds the matching Californium from column I to go across and find the value from column H. In this example, I’m looking in a column to the left. I’ve highlighted the match in yellow.
Once Excel matched Californium, it then looked up the Periodic No. The query returned 98 which shows at the bottom of the argument list as well as the Formula result = line at the bottom.
How to use XLOOKUP
For this tutorial, I’ll be using Microsoft Office 365 and the xlookup-elements-examples.xlsx spreadsheet. You can find in the Additional Resources section. The workbook has 2 sheets. One example is a common lookup whereas the second one includes a horizontal lookup and the formula.
- Download the spreadsheet from the Additional Resources section.
- Select the Vertical worksheet.
- Click in cell C2. This is where we will put the lookup formula.
- Click the Formulas tab.
- Click Insert Function. The Insert Function dialog appears.
- In the Search for a function: textbox type xlookup.
- Click Go.
- Click OK to accept XLOOKUP. A blank Function Arguments dialog appears.
- With your cursor in the Lookup_value field box, click cell B2. This is the field we want to match.
- Tab to advance to Lookup_array. This is the cell range that has the matching values for column B.
- Select cells I2 through I25.
- Press F4 to make an absolute range.
- Tab to advance to Return_array. This is the range that contains our answers.
- Select cells H2 through H25.
- Press F4 to make an absolute range. You should see a result in the Formula result = line.
- Click OK. You should see 98 in cell C2.
- With your cursor in cell C2, press Ctrl + Shift + D to copy the XLOOKUP formula down the column.
Note: If XLOOKUP doesn’t appear then you don’t have the function yet.
And don’t worry if you still want to use the VLOOKUP and HLOOKUP functions. Microsoft doesn’t have any plans to retire these functions.