XLOOKUP with Multiple Criteria in Google Sheets

Google Sheets offers powerful tools for searching and retrieving data, and one such tool is the XLOOKUP function. Although it may not be explicitly designed for handling multiple criteria, we can adapt it using IF and Boolean logic to meet those needs. In this article, we’ll dive into how to use XLOOKUP with multiple criteria in Google Sheets, equipping you with the knowledge to overcome complex data search challenges.

XLOOKUP with Multiple Criteria: Exact Match

Let’s begin by exploring how to perform an exact match using XLOOKUP with multiple criteria. Imagine you have a table with item names, purchase dates, purchase months, and prices. Your goal is to find the price of a specific item in a particular month.

To achieve this, we can use the XLOOKUP formula with the following syntax:

=ArrayFormula(XLOOKUP(1, (A2:A10=F2) * (C2:C10=G2), D2:D10, "", 0, 1))

Now, let’s break down the formula:

• search_key: 1
• lookup_range: (A2:A10=F2) * (C2:C10=G2)
• result_range: D2:D10
• missing_value: “”
• match_mode: 0 (exact match)
• search_mode: 1 (search from the first value to the last value)

The magic lies in the lookup_range. While XLOOKUP requires a single column or row, we need to search in two different columns: the item column and the month column. To overcome this limitation, we combine both criteria columns into a single column using (A2:A10=F2) * (C2:C10=G2) in the lookup_range. The use of the ARRAYFORMULA function with XLOOKUP is necessary because this Boolean logic requires array formula support.

XLOOKUP with Multiple Criteria: Approximate Match

Now, let’s delve into performing an approximate match using XLOOKUP with multiple criteria. In this scenario, let’s say we want to find the price of an item on a specific date or the next available latest date if there is no receipt.

To achieve this, we modify the formula as follows:

=ArrayFormula(XLOOKUP(G2, IF(A2:A10="Mango", B2:B10), D2:D10, "", 1, 1))

In this case, we replace the match_mode with 1, enabling XLOOKUP to execute an approximate match of the search key. To search from the bottom to the top, we can replace the last argument in the formula with -1.

XLOOKUP with Multiple Criteria: Partial Match

Lastly, let’s discuss performing a partial match using XLOOKUP with multiple criteria. Consider a scenario where we have various grade labels for fruits, and we want to look up the price of a specific fruit received in a particular month.

The formula for a partial match would be:

=ArrayFormula(XLOOKUP(1, SEARCH(F2, A2:A10) * (C2:C10=G2), D2:D10, "", 0, 1))

Here, we use the SEARCH function to partially match the first criterion in the item column. The second part, (C2:C10=G2), checks if the month matches and returns TRUE or FALSE. When multiplying, the formula returns 1 or 0, allowing us to look up 1.

To find the price of an item received on a specific date or the next latest available date, we can use the following formula:

=ArrayFormula(XLOOKUP(G2, IF(SEARCH(F2, A2:A10)=1, B2:B10), D2:D10, "", 1, 1))

In this formula, we incorporate the SEARCH function to determine if the item partially matches and use IF to define the lookup range.

Conclusion

XLOOKUP with multiple criteria in Google Sheets is an invaluable technique for searching and retrieving data. By combining IF and Boolean logic, you can unlock advanced search capabilities that go beyond the standard functionality of the XLOOKUP function. With these techniques, you’ll be able to navigate complex data sets and find the information you need efficiently.

To learn more about XLOOKUP and explore additional tips and tricks, you can visit Crawlan.com. Happy sheeting!