How to Use AND and OR in the SEARCH Function in Google Sheets

In Google Sheets, you can check for multiple words in a cell without using the actual AND() or OR() functions. By combining the SEARCH function with array formulas, you can mimic OR logic to see if any word from a list is present, or AND logic to confirm that all required words appear.

In this tutorial, we’ll walk through practical examples of AND OR use in SEARCH function so you can create smarter keyword matching formulas for data validation, filtering, and text analysis.

Table of Contents

Matching Any Word in a Cell with the SEARCH Function (OR Logic)

The OR use in the SEARCH function allows you to check if at least one keyword from your list appears in a given cell.

Example:
Cell A1 contains the text:

train timing

You want to check whether cell A1 contains any of the words:
train, bus, flight, tram, or metro.

You can use this formula:

=SUMPRODUCT(IFERROR(SEARCH(HSTACK("Train", "Flight", "Bus", "Tram", "Metro"), A1)))>0

How it works:

HSTACK("Train", "Flight", "Bus", "Tram", "Metro") creates a horizontal array of the words to search.
SEARCH(..., A1) checks each word against the text in A1 and returns a number if found or an error if not found.
IFERROR(..., ) removes errors, allowing calculations to proceed.
SUMPRODUCT(...)>0 returns TRUE if at least one match exists, otherwise FALSE.

Whole Word Match in OR Logic with SEARCH Function

By default, SEARCH will match partial words. For example, searching "train" would also match "training". If you want whole-word matching, you can add spaces around both the search term and the text being searched:

=SUMPRODUCT(IFERROR(SEARCH(HSTACK(" Train ", " Flight ", " Bus ", " Tram ", " Metro "), " "&A1&" ")))>0

Here:

" "&A1&" " pads the text in A1 with spaces.
" Train " and other terms are padded with spaces to ensure only full words match.

Using a Range Instead of Typing Words (OR Logic)

Instead of typing words directly into HSTACK(...), you can store them in a range (e.g., TOCOL(B2:B10, 3)), which drops empty cells and avoids matching issues.

Why 3?
In TOCOL(array, [ignore]), using 3 ignores blanks and errors.

Partial match:

=SUMPRODUCT(IFERROR(SEARCH(TOCOL(B2:B10, 3), A1)))>0

Whole-word match:

=SUMPRODUCT(IFERROR(SEARCH(" "&TOCOL(B2:B10, 3)&" ", " "&A1&" ")))>0

Matching All Words in a Cell with the SEARCH Function (AND Logic)

The AND use in the SEARCH function checks whether all specified words are present in the text.

Example:
Cell A1 contains:

diesel injection pump

You want to verify that both "diesel" and "pump" are present.

Formula:

=SUMPRODUCT(IFERROR(SEARCH(HSTACK("diesel", "pump"), A1)^0))=2

How it works:

SEARCH(...) returns a number for matches, which becomes 1 when raised to the power of 0 (^0).
If both words are found, the result will be 1 for each word, giving a sum of 2.
The =2 at the end checks that both words matched.

Replace 2 with the actual number of keywords you are searching for.

Whole Word Match in AND Logic with SEARCH Function

For exact word matches:

=SUMPRODUCT(IFERROR(SEARCH(HSTACK(" diesel ", " pump "), " "&A1&" ")^0))=2

Using a Range for AND Logic

If your keywords are stored in a range (e.g., TOCOL(B2:B10, 3)), you can use this structure to ensure all words are present.

Partial match:

=SUMPRODUCT(IFERROR(SEARCH(TOCOL(B2:B10, 3), A1)^0)) = ROWS(TOCOL(B2:B10, 3))

Whole-word match:

=SUMPRODUCT(IFERROR(SEARCH(" "&TOCOL(B2:B10, 3)&" ", " "&A1&" ")^0)) = ROWS(TOCOL(B2:B10, 3))

Note:
This will return TRUE if the criteria range is empty. If you want it to return FALSE when the range is empty, wrap it in:

=IF(COUNTA(B2:B10), <your_formula>, FALSE)

How These SEARCH Formulas Differ from AND() and OR()

While AND() and OR() in Google Sheets evaluate logical tests, using AND OR logic in the SEARCH function lets you directly search for keywords within text strings. This makes them ideal for:

Keyword detection in product descriptions.
Data cleaning and classification.
Automating text-based filters.

Practical Use Cases

Marketing: Find rows where any competitor brand name appears in product listings.
HR: Check if all required skills are listed in a candidate’s resume text.
Education: Identify if assignments contain mandatory keywords.

Alternative Approach Using REGEXMATCH

If you’re comfortable with regular expressions, you can achieve the same AND/OR matching logic using the REGEXMATCH function instead of SEARCH. This can make formulas shorter, but it requires knowledge of regex syntax.

You can learn more in my step-by-step guide here: How to Use REGEXMATCH in Google Sheets.