Use XLOOKUP with multiple criteria

XLOOKUP is a powerful function that was introduced in Excel 2019. It allows you to search for a specific value in a range of cells and return a corresponding value from another range of cells. This function can be very useful when working with large sets of data and can save a significant amount of time compared to using other lookup functions like VLOOKUP and HLOOKUP.

Here’s an example of how to use XLOOKUP in Excel:

Suppose you have a table of sales data for different products and you want to find the total sales for a specific product. The table looks like this:

To use XLOOKUP to find the total sales for Product B, follow these steps:

Step 1: Select the cell where you want to display the total sales for Product B.

Step 2: Type the XLOOKUP function into the formula bar. The syntax of the function is as follows:

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

Step 3: Replace the arguments in the function with the following:

• lookup_value: This is the value you want to search for, which is “Product B” in this case.
• lookup_array: This is the range of cells where you want to search for the lookup_value. In this case, the lookup_array is the range A2:A5, which contains the Product names.
• return_array: This is the range of cells where you want to return the corresponding value. In this case, the return_array is the range B2:E5, which contains the sales data for each Product.
• if_not_found: This is an optional argument that specifies what value to return if the lookup_value is not found in the lookup_array. In this case, we can leave this argument blank.
• match_mode: This is an optional argument that specifies how to match the lookup_value with the values in the lookup_array. The available options are 0 (exact match), -1 (exact or next smaller match), and 1 (exact or next larger match). In this case, we want an exact match, so we can leave this argument blank.
• search_mode: This is an optional argument that specifies the direction of the search in the lookup_array. The available options are 1 (search from the beginning to the end) and -1 (search from the end to the beginning). In this case, we can leave this argument blank.

So, the final XLOOKUP formula looks like this:

=XLOOKUP("Product B", A2:A5, B2:E5)

Step 4: Press Enter to display the total sales for Product B, which is 2000 in Q1, 2500 in Q2, 3000 in Q3, and 3500 in Q4. The XLOOKUP function automatically returns the corresponding value from the return_array, which is the range B2:E5.

That’s it! You can use the XLOOKUP function in a similar way to search for and retrieve data from other tables or ranges of cells. Here are some additional tips and tricks for using XLOOKUP in Excel:

1. Use XLOOKUP with multiple criteria

XLOOKUP can be used to search for data using multiple criteria. To do this, you need to concatenate the criteria into a single cell, separated by a delimiter like a comma or a semicolon, and then use that cell as the lookup_value. For example, suppose you have a table of employee data with columns for First Name, Last Name, and Department. To find the salary of an employee named John Smith in the Sales department, you can use the following formula:

=XLOOKUP("John,Smith,Sales", A2:A10&","&B2:B10&","&C2:C10, D2:D10)

This formula concatenates the values in the First Name, Last Name, and Department columns for each employee and uses them as the lookup_value. The XLOOKUP function searches for this concatenated value in the lookup_array, which is the result of concatenating the values in the First Name, Last Name, and Department columns for all employees. The function then returns the corresponding value from the Salary column.

2. Use XLOOKUP with wildcard characters

XLOOKUP can be used with wildcard characters like the asterisk (*) and the question mark (?) to search for partial matches. For example, suppose you have a table of products with a column for Product Name. To find the sales for all products that contain the word “Widget” in the Product Name, you can use the following formula:

=XLOOKUP("*Widget*", A2:A10, B2:B10)

This formula uses the asterisk (*) as a wildcard character to search for any value that contains the word “Widget” in the lookup_array. The function then returns the corresponding value from the Sales column.

3. Use XLOOKUP with the INDEX function

You can use XLOOKUP with the INDEX function to return a range of cells instead of a single value. To do this, you need to specify the range of cells that you want to return as the return_array argument, and then wrap the XLOOKUP function in the INDEX function. For example, suppose you have a table of sales data with columns for Product Name, Quarter, and Sales. To return the sales data for all products in Q2, you can use the following formula:

=INDEX(B2:E5, , XLOOKUP("Q2", C2:C5, {2,3,4,5}))

This formula uses the XLOOKUP function to search for “Q2” in the Quarter column and return the corresponding column numbers for Q2, which are 2, 3, 4, and 5. These column numbers are then used as the column argument in the INDEX function to return the Sales data for all products in Q2.

4. Use XLOOKUP with dynamic arrays

XLOOKUP is compatible with dynamic arrays in Excel, which means that it can return multiple values if the lookup_value matches multiple values in the lookup_array. To do this, you need to enter the XLOOKUP function into a cell that has enough space to display the results, and then press Enter. Excel will automatically spill the results into adjacent cells. For example, suppose you have a table of sales data with columns for Product Name, Quarter, and Sales. To return the sales data for all products in Q2, you can use the following formula:

=XLOOKUP("Q2", C2:C5, B2:E5)

This formula uses the XLOOKUP function to search for “Q2” in the Quarter column and return the corresponding Sales data for all products in Q2. Because the return_array argument is a range of cells, XLOOKUP returns a dynamic array that spills into adjacent cells.

5. Use XLOOKUP with approximate matches

XLOOKUP can also be used to perform approximate matches using the optional match_mode argument. There are three options for match_mode: 0 (exact match), -1 (exact match or next smallest value), and 1 (exact match or next largest value). For example, suppose you have a table of grades with columns for Percentage and Grade. To find the grade for a percentage of 85%, you can use the following formula:

=XLOOKUP(85%, A2:A6, B2:B6, , 1)

This formula uses XLOOKUP to search for the percentage 85% in the Percentage column and return the corresponding grade using approximate matching. Because the match_mode argument is set to 1, XLOOKUP will return the grade for the next largest value if there is no exact match for 85%.

6. Use XLOOKUP to handle errors

XLOOKUP can also handle errors using the optional if_not_found argument. There are three options for if_not_found: 0 (return #N/A), 1 (return the closest match), and a custom value. For example, suppose you have a table of employee data with columns for First Name, Last Name, and Salary. To find the salary of an employee named Jane Doe, you can use the following formula:

=XLOOKUP("Jane", A2:A10, D2:D10, "", 0, 1, "Not Found")

This formula uses XLOOKUP to search for “Jane” in the First Name column and return the corresponding Salary. If there is no exact match for “Jane”, XLOOKUP will return the closest match using approximate matching (because the match_mode argument is set to 1) or return the custom value “Not Found” if no match is found.

In conclusion, XLOOKUP is a powerful and flexible function in Excel that can help you search for and retrieve data from tables or ranges of cells. By using XLOOKUP with multiple criteria, wildcard characters, the INDEX function, dynamic arrays, approximate matches, and error handling, you can easily manipulate and analyze large amounts of data in Excel.