Site icon Professor Excel

XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!

XLOOKUP in Excel

XLOOKUP in Excel

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.

Overview of the big XLOOKUP series

XLOOKUP: Introduction

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.

Arguments of XLOOKUP

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.

Syntax of XLOOKUP: The XLOOKUP formula can have up to six arguments. For now, we only concentrate on the required, first three arguments.
  1. The first argument is the search or lookup value. It’s the value you look for in your Excel table.
  2. The search area is a range of cells (e.g. row or column) you want to find your search value in.
  3. Return area is also the range of cells you want to have a value returned from.

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

Examples

Let’s start with some examples.

Example 1: Basic XLOOKUP

Simple XLOOKUP example.

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

Example 2: Basic XLOOKUP for horizontal lookups

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:

Example for a horizontal XLOOKUP function.

The arguments for the XLOOKUP function are as follows:

  1. The first argument is the search value. So, what do you search for? It’s the month, in this case provided in cell C3.
  2. Second argument is the search array. Where do you search in? It’s the month names in the headline of the date, cell range C10 to O10.
  3. And last: What value do you want to get returned? The revenue is in row 11. So the return range is C11 to O11.

Putting everything together leads to function =XLOOKUP(C3,C10:O10,C11:O11).

Example 3: XLOOKUP for multiple search criteria

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.

XLOOKUP with multiple search criteria.

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.

Download

Please feel free to download all examples from above in this notebook. Follow this link and the download starts right away.

Exit mobile version