XLOOKUP: Return a corresponding value from an array

The XLOOKUP function is a powerful tool that allows you to look up values in a table or range and return corresponding values from a specified column. It is a new function introduced in Excel 365, and it is an improved version of the VLOOKUP function. The XLOOKUP function has several advantages over the VLOOKUP function, including the ability to perform approximate and exact matches, search from the right or left of the table, and return multiple values.

Let’s start with some basic syntax and examples of how to use the XLOOKUP function.

Syntax:

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

The XLOOKUP function has five arguments:

1. lookup_value: This is the value you want to look up in the table or range.
2. lookup_array: This is the range or array of values you want to search for the lookup_value.
3. return_array: This is the range or array of values you want to return when a match is found.
4. match_mode: This is an optional argument that specifies whether you want an exact or approximate match. The default is an exact match. There are four match modes to choose from: 0 (exact match), -1 (exact match or next smaller value), 1 (exact match or next larger value), and 2 (wildcard match).
5. search_mode: This is an optional argument that specifies whether you want to search from the left or right of the lookup_array. The default is to search from the left. There are two search modes to choose from: 1 (search from the left) and -1 (search from the right).

Now let’s move on to some examples of how to use the XLOOKUP function.

Example 1: Exact match with one lookup_value and one return_value

In this example, we will use the XLOOKUP function to look up a specific value in a table and return a corresponding value.

Suppose we have a table of employee information that includes their names, employee IDs, and salaries. We want to find the salary of an employee whose ID is 12345.

Here’s how we can use the XLOOKUP function to find the employee’s salary:

=XLOOKUP(12345, B2:B6, C2:C6)

In this formula, 12345 is the lookup_value, B2:B6 is the lookup_array (which contains the employee IDs), and C2:C6 is the return_array (which contains the employee salaries). When we enter this formula into a cell, Excel will search for the value 12345 in the range B2:B6 and return the corresponding salary from C2:C6.

Example 2: Exact match with multiple lookup_values and one return_value

In this example, we will use the XLOOKUP function to look up multiple values in a table and return corresponding values.

Suppose we have a table of product information that includes product names, SKUs, and prices. We want to find the prices of several products whose SKUs are listed in a separate table.

Here’s how we can use the XLOOKUP function to find the prices of multiple products:

=XLOOKUP(D2:D5, B2:B6, C2:C6)

In this formula, D2:D5 is the lookup_value array (which contains the SKUs we want to look up), B2:B6 is the lookup_array (which contains the SKUs in the product information table), and C2:C6 is the return_array (which contains the prices of the products). When we enter this formula into a cell, Excel will search for each SKU in the range B2:B6 and return the corresponding price from C2:C6. The result will be an array of prices that corresponds to the SKUs we looked up.

Example 3: Approximate match with one lookup_value and one return_value

In this example, we will use the XLOOKUP function to perform an approximate match and return a corresponding value.

Suppose we have a table of grade cutoffs for a test that determines letter grades. We want to find the letter grade of a student who scored 85 on the test.

Here’s how we can use the XLOOKUP function to perform an approximate match and find the corresponding letter grade:

=XLOOKUP(85, D2:D6, E2:E6, 1)

In this formula, 85 is the lookup_value, D2:D6 is the lookup_array (which contains the grade cutoffs), E2:E6 is the return_array (which contains the letter grades), and 1 is the match_mode (which specifies an approximate match). When we enter this formula into a cell, Excel will search for the value 85 in the range D2:D6 and return the corresponding letter grade from E2:E6 based on the cutoffs.

Example 4: Wildcard match with one lookup_value and one return_value

In this example, we will use the XLOOKUP function to perform a wildcard match and return a corresponding value.

Suppose we have a table of book titles and their authors. We want to find the author of a book whose title contains the word “Gone”.

Here’s how we can use the XLOOKUP function to perform a wildcard match and find the corresponding author:

=XLOOKUP("Gone", A2:A6, B2:B6, 2)

In this formula, “Gone” is the lookup_value, A2:A6 is the lookup_array (which contains the book titles), B2:B6 is the return_array (which contains the authors), and 2 is the match_mode (which specifies a wildcard match). When we enter this formula into a cell, Excel will search for any value in the range A2:A6 that contains the word “Gone” and return the corresponding author from B2:B6.

Example 5: Searching from the right of the lookup_array

In this example, we will use the XLOOKUP function to search from the right of the lookup_array and return a corresponding value.

Suppose we have a table of employee information that includes their names, job titles, and salaries. We want to find the job title of an employee whose salary is $80,000.

Here’s how we can use the XLOOKUP function to search from the right of the lookup_array and find the corresponding job title:

=XLOOKUP(80000, C2:C6, B2:B6, , -1)

In this formula, 80000 is the lookup_value, C2:C6 is the lookup_array (which contains the salaries), B2:B6 is the return_array (which contains the job titles), and -1 is the search_mode (which specifies to search from the right of the lookup_array). When we enter this formula into a cell, Excel will search for the value 80000 in the range C2:C6 from the right and return the corresponding job title from B2:B6.

In conclusion, the XLOOKUP function in Excel is a powerful tool that allows you to look up values in a table or range and return corresponding values from a specified column. It has several advantages over the VLOOKUP function and can perform exact and approximate matches, wildcard matches, and search from the left or right of the table. By using the examples provided in this tutorial, you should be able to apply the XLOOKUP function to your own data and improve your productivity in Excel.