Use Microsoft Excel XLOOKUP to lookup values based on multiple criteria

XLOOKUP with multiple criteria is a powerful and versatile function in Microsoft Excel that allows users to lookup values from a table based on multiple criteria. With XLOOKUP, you can retrieve data that meets specific conditions from large data sets without the need for complex formulas or multiple nested IF statements. In this tutorial, we will explain how to use XLOOKUP with multiple criteria in Excel and provide examples of how to apply this function to real-life scenarios.

Understanding the basics of XLOOKUP

XLOOKUP is a dynamic and flexible function that enables users to perform lookups based on various criteria. To get started with XLOOKUP, you need to understand the syntax of the function:

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

• lookup_value is the value or cell that contains the value you want to lookup.
• lookup_array is the range of cells that contain the values you want to match against the lookup_value.
• return_array is the range of cells that contain the values you want to return.
• if_not_found is an optional argument that specifies what value to return if the lookup_value is not found.
• match_mode is an optional argument that specifies how to match the lookup_value with the lookup_array. The options are:
  • 0 or exact match: match the lookup_value exactly with the values in the lookup_array.
  • 1 or next smaller: match the lookup_value with the closest value that is smaller than the value in the lookup_array.
  • -1 or next larger: match the lookup_value with the closest value that is larger than the value in the lookup_array.
  • 2 or wildcard: match the lookup_value with the values in the lookup_array that contain wildcard characters.
• search_mode is an optional argument that specifies the direction of the search in the lookup_array. The options are:
  • 1 or search from start: search from the beginning of the lookup_array.
  • -1 or search from end: search from the end of the lookup_array.

With this syntax in mind, let’s dive into using XLOOKUP with multiple criteria.

Using XLOOKUP with multiple criteria

Suppose you have a large data set that contains information about customers, including their names, cities, and sales figures. You want to retrieve the sales figures for a specific customer in a specific city. To accomplish this, you can use XLOOKUP with multiple criteria.

Here is an example table:

To retrieve the sales figure for Alice in New York, you can use the following XLOOKUP formula:

=XLOOKUP(1, (A2:A5="Alice")*(B2:B5="New York"), C2:C5)

This formula works by multiplying two arrays together: one that checks for the name “Alice” in the Name column and one that checks for the city “New York” in the City column. The result is an array of TRUE and FALSE values that identify the rows that match both criteria. The XLOOKUP function then uses this array to retrieve the sales figure from the Sales column.

Note that the lookup_value in this formula is set to 1, which is arbitrary. This is because the XLOOKUP function does not require an actual lookup value, but rather an array of TRUE and FALSE values.

**Using XLOOKUP with multiple criteria in different columns

In some cases, you may need to use multiple criteria that are located in different columns. To illustrate this, let’s suppose you have a table that contains information about product sales, including the product name, the month of sale, and the sales figure. You want to retrieve the sales figure for a specific product in a specific month.

Here is an example table:

To retrieve the sales figure for Product A in February, you can use the following XLOOKUP formula:

=XLOOKUP(1, (A2:A5="Product A")*(B2:B5="Feb"), C2:C5)

This formula works in the same way as the previous example, but with the addition of a second criteria that checks for the month “Feb” in the Month column.

Using XLOOKUP with multiple criteria and partial matches

XLOOKUP also allows you to use partial matches in your criteria using the match_mode argument. This can be useful when you are working with data that contains variations of a specific value, such as different spellings or abbreviations.

To illustrate this, let’s suppose you have a table that contains information about employees, including their names and job titles. You want to retrieve the job title for an employee whose name contains the string “John”.

Here is an example table:

To retrieve the job title for an employee whose name contains the string “John”, you can use the following XLOOKUP formula:

=XLOOKUP(1, SEARCH("John", A2:A4)>0, B2:B4, "Not found", 0, -1)

This formula uses the SEARCH function to search for the string “John” in the Name column. The SEARCH function returns an array of positions where the string “John” is found. The XLOOKUP function then uses this array to retrieve the job title from the Job Title column.

Note that the match_mode argument is set to 0, which forces an exact match. The search_mode argument is set to -1, which searches from the end of the lookup_array.

Conclusion

XLOOKUP with multiple criteria is a versatile function that enables you to retrieve data that meets specific conditions from large data sets. By understanding the syntax of the function and its various arguments, you can use XLOOKUP to perform lookups based on multiple criteria, criteria in different columns, and even partial matches. With this powerful tool at your disposal, you can save time and improve the accuracy of your data analysis in Microsoft Excel.