

It seems as if Microsoft has listened to many complaints of Excel users and introduced a new formula: XLOOKUP. It’s supposed to improve all the disadvantages of the “traditional” lookup functions VLOOKUP, INDEX/MATCH and SUMIFS. This article describes in what case and how to use it. Please feel also free to download all example in an Excel workbook at the end of this article.
The XLOOKUP function is special in a way that it is very simple in it’s basic form. Just three arguments that are quite straight-forward. At the same time, it has very advanced capabilities. That’s why I’ve decided to split this article: The basic application, just using the first three arguments and advanced functions.
Contents
Microsoft describes XLOOKUP as follows:
“Searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used”
Well, this sounds quite dry. To put it more exciting words: It can do everything VLOOKUP (and mostly also INDEX/MATCH) can do. But much more! I’m sure, this will be the future of lookups in Excel. Before we start, let me already summarize, why I think that XLOOKUP is a great replacement for VLOOKUP (and HLOOKUP, for that matter):
Before we jump into the basic usage, one more word of caution: XLOOKUP is a great function, for sure it’ll be the future. But please – as of now – use it carefully: Make sure that everybody working with your Excel file runs the latest Excel version and can understand your function.
The XLOOKUP function can have up to six arguments. However, only the first three arguments are required – the last three ones are optional. The following image shows the structure of XLOOKUP. Required parts are green and optional arguments grey.
For now, we’ll concentrate on the first three arguments. We take a deeper look at the last three, crossed out arguments, in a separate article (in preparation).
Let’s start with some examples.
The first example is quite simple: You want to look up a currency exchange rate in a simple table as shown in the screenshot. In this example, you look for Danish Krones (DKK).
So, you look for DKK which is written in cell F3 within the cell range of B4 to B9. That means that these are your two first arguments already. Then, as the third argument, you just specify the return area – from which cell range do you want to get the return value? In this case, it’s in cell range C4 to C9. As a result, the function is “=XLOOKUP(F3,B4:B9,C4:C9)”.
It’s simple, isn’t it? Especially when comparing to VLOOKUP…
Our next example is also easy. The only difference is that it works horizontally. Assuming that you have monthly revenue values as shown in the screenshot below:
The arguments for the XLOOKUP function are as follows:
Putting everything together leads to function =XLOOKUP(C3,C10:O10,C11:O11).
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
If I might criticize the XLOOKUP function a little bit: That’s the only thing that isn’t really solved yet when it comes to the disadvantages of VLOOKUP and INDEX/MATCH. Searching for multiple criteria still works the same way as before: Concatenate the search criteria and insert a new search column (or row, depending on your lookup direction).
The example is as follows: You have a table of VAT rates per month and country. Depending on a selected month and country, you want to return the respective VAT rate.
Let’s explore it step-by-step:
When we put these things together, the XLOOKUP function is: “=XLOOKUP(H3&” – “&H4,B:B,E:E)”.
For comparison, I’ve also included solutions using VLOOKUP , SUMIFS and INDEX/MATCH.
Please feel free to download all examples from above in this notebook. Follow this link and the download starts right away.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 2
Julian
Fantastic! Great news!
Henrik Schiffner
That’s what I think… 🙂