Basic overview of the XLOOKUP function

XLOOKUP is a powerful function in Microsoft Excel that allows you to look up and retrieve information from a table quickly and easily. It is an upgraded version of the VLOOKUP and HLOOKUP functions, and it comes with several improvements that make it much more versatile and flexible.

In this tutorial, we will explore the XLOOKUP function in detail and provide you with several examples that will help you understand how it works.

Before we dive into the examples, let’s start with a brief overview of the XLOOKUP function.

What is XLOOKUP?

The XLOOKUP function is a lookup and reference function in Microsoft Excel. It is used to search for a specific value in a range or an array and return a corresponding value from the same or another range or array. It is a powerful alternative to the VLOOKUP and HLOOKUP functions, and it offers several improvements, including:

• The ability to search for values to the left of the lookup column.
• The ability to search for values using wildcards, such as * and ?.
• The ability to return multiple items from a single lookup value.
• The ability to handle errors more gracefully.

XLOOKUP Syntax

The XLOOKUP function has the following syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here’s what each argument represents:

• lookup_value: The value that you want to look up.
• lookup_array: The range or array that you want to search in.
• return_array: The range or array that you want to return a value from.
• if_not_found (optional): The value that you want to return if the lookup value is not found.
• match_mode (optional): The type of match that you want to perform. This can be exact or approximate. If omitted, XLOOKUP defaults to exact match mode.
• search_mode (optional): The direction of the search. This can be from left to right or from right to left. If omitted, XLOOKUP defaults to searching from left to right.

Note: The if_not_found, match_mode, and search_mode arguments are optional, but if you want to use any of them, you need to include all the arguments that come before them.

Example 1: Basic XLOOKUP

Let’s start with a basic example to understand how the XLOOKUP function works.

Suppose we have a table of employees with their names, ages, and salaries, as shown below:

Now, let’s say we want to look up the salary of Bob Smith. We can use the XLOOKUP function to do this as follows:

=XLOOKUP("Bob Smith", A2:A4, C2:C4)

Here’s what this formula does:

• lookup_value: “Bob Smith”
• lookup_array: A2:A4 (the range that contains the names)
• return_array: C2:C4 (the range that contains the salaries)

The XLOOKUP function looks up “Bob Smith” in the range A2:A4 and returns the corresponding salary from the range C2:C4, which is 60000.

Note that the XLOOKUP function automatically defaults to exact match mode, so we don’t need to include the match_mode argument.

Example 2: XLOOKUP with Wildcard

In Example 1, we used the XLOOKUP function to look up an exact match. However, the XLOOKUP function can also handle wildcards, such as * and ?, to search for partial matches.

Let’s use the same employee table as in Example 1, but this time, we want to look up the salary of all employees whose names start with “J”. We can use the following formula:

=XLOOKUP("J*", A2:A4, C2:C4)

Here’s what this formula does:

• lookup_value: “J*”
• lookup_array: A2:A4 (the range that contains the names)
• return_array: C2:C4 (the range that contains the salaries)

The XLOOKUP function searches for any name that starts with “J” in the range A2:A4 and returns the corresponding salary from the range C2:C4. In this case, it returns two salaries: 50000 and 40000.

Note that we used the * wildcard to match any character that comes after the “J”. If we only wanted to match names that start with “J” followed by one character, we could have used the ? wildcard instead.

Example 3: XLOOKUP with Exact Match Mode

In Example 1, we used the XLOOKUP function with the default exact match mode. However, we can also specify the match mode explicitly using the match_mode argument.

Let’s use the same employee table as in Example 1, but this time, we want to look up the age of Jane Doe using exact match mode. We can use the following formula:

=XLOOKUP("Jane Doe", A2:A4, B2:B4, "Not Found", 0)

Here’s what this formula does:

• lookup_value: “Jane Doe”
• lookup_array: A2:A4 (the range that contains the names)
• return_array: B2:B4 (the range that contains the ages)
• if_not_found: “Not Found” (the value to return if the lookup value is not found)
• match_mode: 0 (exact match mode)

The XLOOKUP function looks up “Jane Doe” in the range A2:A4 using exact match mode and returns the corresponding age from the range B2:B4, which is 25.

Note that we used the if_not_found argument to specify the value to return if the lookup value is not found. In this case, if we had looked up a name that wasn’t in the table, the XLOOKUP function would return “Not Found”.

Example 4: XLOOKUP with Approximate Match Mode

In addition to exact match mode, the XLOOKUP function also supports approximate match mode. Approximate match mode is useful when you want to find a value that is closest to, but not exactly equal to, the lookup value.

Let’s use the same employee table as in Example 1, but this time, we want to look up the salary of an employee whose age is closest to 32. We can use the following formula:

=XLOOKUP(32, B2:B4, C2:C4, "Not Found", 1)

Here’s what this formula does:

• lookup_value: 32
• lookup_array: B2:B4 (the range that contains the ages)
• return_array: C2:C4 (the range that contains the salaries)
• if_not_found: “Not Found” (the value to return if the lookup value is not found)
• match_mode: 1 (approximate match mode)

The XLOOKUP function searches for the closest age to 32 in the range B2:B4 and returns the corresponding salary from the range C2:C4, which is 60000.

Note that we used the match_mode argument to specify approximate match mode. In this mode, the XLOOKUP function looks for the value that is closest to the lookup value, but not greater than it (i.e., it looks for the largest value that is less than or equal to the lookup value). If all values in the lookup array are greater than the lookup value, the XLOOKUP function returns the if_not_found value.

Example 5: XLOOKUP with Range Lookup

In addition to exact and approximate match modes, the XLOOKUP function also supports range lookup. Range lookup is useful when you want to look up a value within a range of values.

Let’s use the following table, which shows the commission rates for different sales amounts:

Suppose we want to look up the commission rate for a sales amount of 20000. We can use the following formula:

=XLOOKUP(20000, A2:A5, B2:B5, "Not Found", -1)

Here’s what this formula does:

• lookup_value: 20000
• lookup_array: A2:A5 (the range that contains the sales amounts)
• return_array: B2:B5 (the range that contains the commission rates)
• if_not_found: “Not Found” (the value to return if the lookup value is not found)
• match_mode: -1 (range lookup mode)

The XLOOKUP function searches for the sales amount of 20000 in the range A2:A5 using range lookup mode and returns the corresponding commission rate from the range B2:B5, which is 10%.

Note that we used the match_mode argument to specify range lookup mode. In this mode, the XLOOKUP function looks for the largest value in the lookup array that is less than or equal to the lookup value (i.e., the lower boundary of the range). It then returns the value from the same position in the return array. If all values in the lookup array are greater than the lookup value, the XLOOKUP function returns the if_not_found value.

In summary, the XLOOKUP function is a powerful and flexible tool for looking up values in Excel. It can handle exact and approximate matches, wildcards, and range lookups, making it a versatile alternative to the traditional VLOOKUP and HLOOKUP functions.