In this tutorial, we will show you exactly how to use an XLOOKUP function with multiple criteria in Google Sheets in just a few simple steps. Simply follow the steps below.
The XLOOKUP function in Google Sheets lets you search for a value in one column or range (the lookup range) and return a corresponding value from another column or range (the result range). To use an XLOOKUP with multiple criteria, combine those criteria into a single lookup criterion that Excel can process.
Here's the general syntax for using an XLOOKUP with the ARRAYFORMULA with multiple criteria:
```=(XLOOKUP(search_key1 & search_key2, lookup_range1 & lookup_range2, result_range, [missing_value], [match_mode], [search_mode])```
Where:
search_key1 & search_key2 are the values you're searching for.
lookup_range1 & lookup_range2 are the range or array where you want to search for the search_key.
result_range is the range or array from which you want to return a value once the search_key is found.
missing_value (optional) is the value to return if no match is found (e.g., "Not found").
match_mode (optional) defines whether you want an exact or approximate match.
search_mode (optional) determines the direction of the search.
Follow the process below on how to apply the XLOOKUP function with multiple criteria in Google Sheets manually.
In your Google Sheets file, select the entire column where you want your results (e.g., column C). Now, begin your formula with an ARRAYFORMULA to apply it to a range of cells.
Type: =ARRAYFORMULA(. This allows you to apply the formula to multiple rows at once, automatically evaluating each row without having to manually drag the formula down.
Within an ARRAYFORMULA, type your XLOOKUP formula. Combine the Product Name and Category for the lookup. Here’s an example where we want to find the Quantity based on both Product Name and Category:
```=ARRAYFORMULA(XLOOKUP(A12&B12, A2:A6&D2:D6, B2:B6, "No math found", 0, 1))```
Where:
This formula applies the lookup across all rows in one go.
Since this formula applies to a range, you don’t need to specify individual criteria for each row. The formula automatically combines each row’s Product Name and Category and performs the search.
For example, it will check for "Grape" in "Fruit" and return the corresponding Quantity for each row.
If no match is found for certain rows, the formula will return "No match found." You can customize this error message by changing the fourth argument of the XLOOKUP. For example:
```=ARRAYFORMULA(XLOOKUP(A2:A10 & D2:D10, A2:A10 & D2:D10, B2:B10, "Product not available", 0, 1))```
This will display "Product not available" when there’s no matching product and category.
You can input "Tomato" on A12 and "Fruit" on B12 to verify if the "Product not available" message will show after editing the syntax.
Once you’ve created this ARRAYFORMULA with the XLOOKUP, it will automatically apply to all rows in the specified range (A2 and D2). You don’t need to manually drag the formula down—just expand the ranges if your data grows, and it will automatically apply to new rows.
We hope that you now have a better understanding of how you can make use of XLOOKUP function with multiple conditions in Google Sheets. If you enjoyed this article, you might want to check out our articles on how to use INDEX and MATCH with multiple criteria in Google Sheets or how to see who has access to a Google Sheet.
