In this article:

XLOOKUP With Multiple Criteria on Google Sheets (2024)

October 1, 2024

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.

xlookup google sheets multiple criteria

XLOOKUP With Multiple Conditions

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.

Syntax

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.

  • 0 (default) = exact match


search_mode
(optional) determines the direction of the search.

  • 1 (default) = search from first to last (top to bottom, or left to right)

How to Use an XLOOKUP With Multiple Criteria in Google Sheets

Follow the process below on how to apply the XLOOKUP function with multiple criteria in Google Sheets manually.

1. Select the Destination Column for Your Results and Start With an ARRAYFORMULA

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.

xlookup with multiple parameters in google sheets

2. Write the XLOOKUP Function Inside ARRAYFORMULA

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:

  • A12 & B12: This combines the Product Name and Category for all rows as the search key.
  • A2:A6&D2:D6: This is the lookup range, combining the Product Name and Category for matching.
  • B2:B6: This is the result range, which returns the Quantity.
  • "No match found": Custom error message for unmatched rows.
  • 0: Exact match required.
  • 1: Search from the first row downward.

This formula applies the lookup across all rows in one go.

multiple criteria google sheets xlookup

3. Adjust Search Criteria Dynamically

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.

4. Customize the Missing Value Message

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.

5. Test and Apply to More Rows Automatically

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->