Step-by-Step Guide to Mastering the new XLOOKUP Function

If you’ve used functions for a little while, no doubt you will have seen or used VLOOKUP. It’s a really popular function and one of the first that most people start using but it does come with some limitations.

Fortunately, we have a new function on the blocks which doesn’t have these limitations and does so much more. I introduce XLOOKUP!

Syntax

There are 3 obligatory parameters and 3 optional ones.

Example 1 – Looking up data to the right in a table

Let’s start with the basic use of XLOOKUP. Below, we have a table of products with prices of each one. I want to look up products from a drop-down menu and get its price.

In cell E2 I have an XLOOKUP function which is looking up the price of the product selected in cell D2.

=XLOOKUP(D2, A2:A5, B2:B5)

Search_key: We state what we’re looking for. For example, the contents of D2.

Lookup_range: We state the range where we’re going to look for the search key. For example, the column of products in A2:A5.

Result_range: We state the range where the data is that we want to return. For example, the prices in B2:B5.

As we can see in the example above, it’s found the washing machine and returned the correct price.

Important! Make sure the range sizes of the lookup_range and result_range are the same, otherwise, it will display an error.

Example 2 – Looking up data to the left

In this example, we’re going to look up students by their rank in a series of sports. We’ll enter the rank we’re looking for and XLOOKUP will look left and return the name of the student at that rank.

In E2 we choose the rank from the drop-down, and it will return the student at that rank in D2.

=XLOOKUP(E2, B2:B6, A2:A6)

Search_key: We state what rank we’re looking for, i.e. the contents of D2.

Lookup_range: We state the range where the rank is, i.e. in B2:A6.

Result_range: We state the range where the students are what we want to return, i.e. in A2:A6.

As we can see, Georgina Harris is in rank 3.

You will also notice that the ranges are the opposite way round to example 1, as we’re looking left not right to get our data. Great! No need to use the more complicated INDEX MATCH combination.

Example 3 – Dealing with values not found

3.1

In this example, we’re going to get the weekly price based on the number of weeks entered.

In the table above, we have prices for up to 5 weeks and if we enter the number of weeks in E2 it will return the price in F2.

3.2

But what happens if some enters more than 5 weeks? Ah, we get an error as we don’t have a price for that number of weeks. Let’s use an additional argument in XLOOKUP to deal with the error.

=XLOOKUP(E2, B2:B6, C2:C6, “Price not found”)

Now, we add a fourth one, [missing_value], which is the text that will be displayed if the lookup doesn’t find the search key in the data range.

As you can see, it didn’t find 6 weeks but displays the “Price not found” message instead of the ugly error.

Example 4 – Exact matches

Here, we’re going to look at the optional fifth argument, which controls whether you’re looking for an exact match or for the closest one, either higher or lower.

4.1

On the left, we have a table showing language levels and their corresponding scores. On the right, we enter a score to determine their level.

You can see that if they get 51, they are Advanced level, as it will look up to the nearest higher value in the table. This is useful when the scores represent the maximum values for each band.

In E2 we enter the following formula:

=XLOOKUP(D2, A2:A4, B2:B4, “Level not found”, 1)

We get the score in D2, then look up the value in column A and if there’s no exact match it gets the nearest higher value, then gets the corresponding value in column B. The fifth argument, 1, is the match mode and tells it to look for a higher value.

There are 4 match mode options:

0: Exact match – Default if option is absent.

1: Exact match or if not present, the next value higher than the search_key.

-1: Exact match or if not present, the next value lower than the search_key.

2: Wildcard match

4.2

This time we have a similar situation but with different scores and we want to get the nearest lower score if there isn’t an exact match.

In E7 we have the following XLOOKUP formula:

=XLOOKUP(D7, A7:A9, B7:B9, “Level not found”, –1)

The first 4 parameters work in the same way as before, just are different cells and ranges. The fifth argument is -1 which is telling it to look for the nearest lower value if there’s no exact match.

So, you can see that if they get 31 they are Intermediate level, as it will look up to the nearest lower value in the table. This is useful when the scores represent the minimum values for each band.

Example 5 – Using wildcards in the search key

The final option in the match mode we looked at in Example 5 above is the wildcard match. Let’s look at a couple of examples where we can use this to find data without having to enter the exact match.

We have two wildcard characters:

* An asterisk means any characters and any number of characters.

? A question mark means any character but represents only one character. We can put multiple question marks to dictate how many characters we want. For example, ??? means any 3 characters but it has to be 3 characters, no more and no less.

5.1

Here, we have a list of 4 classes and their teachers. We want to find out which class a teacher has but without having to type in their full name.

Before we look at the final XLOOKUP formula we have in cell E2, let’s simplify it a little to show how the wildcard more clearly.

=XLOOKUP(“*Wilma*”,B2:B5,A2:A5, “Not Found”, 2)

The above formula will look up any teacher with the name Wilma somewhere in their name. The asterisk at the start means that it doesn’t matter what’s before and the asterisk at the end means it doesn’t matter what’s at the end. The first asterisk is a little redundant in this example but I wanted to show its use.

So, it would find texts like: “Evans, Wilma”, “Wilma Evans”, “Evans, Wilma2”, etc.

5.2

We don’t want to hard wire the name in the formula, so let’s change the string to a cell reference.

=XLOOKUP(“*”&E1&“*”, B2:B5, A2:A5, “Not Found”, 2)

This time we’re looking at the string in cell E1. We put the asterisks in speech marks as they are strings and then concatenate them to the cell with the ampersands (&).

We enter the name we’re looking for and it will find the class that Wilma is in.

Note, that it isn’t case-sensitive. If we had two Wilma’s it would only find the first one in the range. So, we would have to have a wider search key.

Example 6 – Returning multiple results

So far, we’ve been returning results in a single cell but we can return a number of cells with just one lookup.

Below we have some clothes at a warehouse and the details of each one, Code, Colour, Stock, and Price, and we want to be able to find out those details by entering the code.

In H2 we enter the following formula:

=XLOOKUP(G2, A2:A5, B2:E5)

We look up the code in column A but then return the values in columns B to E in the same row as the matched value, across different cells.

This post provides a condensed overview of the XLOOKUP function. For more in-depth information, check out my book, “Google Sheets Functions 2.”