Use XLOOKUP to return multiple values, approximate matches and wildcard matches

The XLOOKUP function was introduced in Excel 365 and is an extremely powerful lookup function that allows you to search for a specific value in a table or range and return a corresponding value from the same or another column. XLOOKUP can be used as a replacement for several other lookup functions such as VLOOKUP, HLOOKUP, INDEX-MATCH, and even the more advanced lookup functions like CHOOSE and OFFSET.

Syntax: The syntax for XLOOKUP function is as follows:

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

Where:

• lookup_value (required) – the value you want to search for in the lookup_array
• lookup_array (required) – the range or array of cells that contains the values to be searched
• return_array (required) – the range or array of cells that contains the values to be returned
• not_found (optional) – the value to be returned if the lookup_value is not found (defaults to #N/A if not specified)
• match_mode (optional) – determines the type of match to be performed (exact match, wildcard match, or approximate match)
• search_mode (optional) – determines the direction of the search (search from beginning to end, or from end to beginning)

Now, let’s look at some examples to understand the function better.

Example 1: Simple Exact Match Suppose you have a table with employee names in column A and their respective salaries in column B. You want to find the salary of an employee named “John”. The XLOOKUP formula for this scenario would be:

=XLOOKUP("John", A2:A10, B2:B10)

In this formula, we have specified the lookup_value as “John”, the lookup_array as A2:A10 (the range containing the names of the employees), and the return_array as B2:B10 (the range containing the salaries of the employees). The formula will search for the value “John” in the lookup_array and return the corresponding value from the return_array.

Example 2: Wildcard Match Suppose you have a table with product names in column A and their respective prices in column B. You want to find the price of a product that contains the word “Apple”. The XLOOKUP formula for this scenario would be:

=XLOOKUP("*Apple*", A2:A10, B2:B10, "Not Found", 2)

In this formula, we have specified the lookup_value as “Apple“, the lookup_array as A2:A10 (the range containing the names of the products), and the return_array as B2:B10 (the range containing the prices of the products). We have also specified the match_mode as 2, which means that we want to perform a wildcard match. The formula will search for any value in the lookup_array that contains the word “Apple” and return the corresponding value from the return_array. If the value is not found, it will return the string “Not Found”.

Example 3: Approximate Match Suppose you have a table with product names in column A and their respective weights in column B. You want to find the weight of a product whose name is closest to “Orange”. The XLOOKUP formula for this scenario would be:

=XLOOKUP("Orange", A2:A10, B2:B10, , 1, -1)

In this formula, we have specified the lookup_value as “Orange”, the lookup_array as A2:A10

(the range containing the names of the products), and the return_array as B2:B10 (the range containing the weights of the products). We have also specified the match_mode as 1, which means that we want to perform an approximate match. The formula will search for the value in the lookup_array that is closest to “Orange” and return the corresponding value from the return_array. We have also specified the search_mode as -1, which means that the search will be performed from right to left.

Example 4: Returning Multiple Values Suppose you have a table with student names in column A and their respective grades in columns B, C, and D for different subjects. You want to find the grades of a student named “John” in all three subjects. The XLOOKUP formula for this scenario would be:

=XLOOKUP("John", A2:A10, B2:D10)

In this formula, we have specified the lookup_value as “John”, the lookup_array as A2:A10 (the range containing the names of the students), and the return_array as B2:D10 (the range containing the grades of the students in all three subjects). The formula will search for the value “John” in the lookup_array and return the corresponding values from the return_array for all three subjects.

Example 5: Returning a Range Suppose you have a table with dates in column A and their respective sales in column B. You want to find the sales for the period between two dates – 01/01/2022 and 03/31/2022. The XLOOKUP formula for this scenario would be:

=XLOOKUP({"01/01/2022","03/31/2022"}, A2:A100, B2:B100)

In this formula, we have specified the lookup_value as {“01/01/2022″,”03/31/2022”}, which is an array of two dates. The lookup_array is A2:A100 (the range containing the dates) and the return_array is B2:B100 (the range containing the sales). The formula will search for both the dates in the lookup_array and return a range of corresponding values from the return_array.

Conclusion:

XLOOKUP is an incredibly versatile function that can be used in a variety of scenarios to search and return values from a table or range. It has several advantages over other lookup functions like VLOOKUP and INDEX-MATCH, including the ability to perform approximate matches, wildcard matches, and return multiple values. By mastering the XLOOKUP function, you can save time and effort when working with large data sets in Excel.