Logical AND, OR Use in SEARCH Function in Google Sheets

Published on

This post explains how to use AND, OR in the SEARCH function in Google Sheets.

Normally in most of the logical formulas, we use the plus (+) sign for the OR use and an asterisk (*) for the AND use. But the fact here is that we will use none of them with the SEARCH function.

Interesting, right?

First, you may go through some examples of the AND, OR functions in standalone use. Understanding them will clear your doubts about why I am not using them with the SEARCH function.

The OR returns TRUE if any of the arguments are TRUE in a logical test.

=or(F4="apple",F4="orange")

This we can replace with the below formula which would return a value greater than zero if any of the arguments are logically TRUE.

=(F4="apple")+(F4="orange")

The AND returns TRUE if all of the arguments are TRUE in a logical test.

=and(F4="apple",F5="orange")

The same formula, we can rewrite using the asterisk operator as below.

=(F4="apple")*(F5="orange")

If the result is 1, that means all of the arguments are TRUE.

Here, in the AND use, the formula tests the values from two different cells, not from a single cell.

The SEARCH function is for partial matches.

So in AND use in the SEARCH in Google Sheets, we can refer to the same cell twice for the logical test. I mean, both the strings “apple” and “orange” can be checked in cell F4 itself.

In AND, OR Use in SEARCH Function in Google Sheets, we won’t use the AND, OR functions or its alternative operators! I’ll explain the same with a few examples below.

AND, OR Use in SEARCH Function (Alternatives)

Here I am going to give more importance to the OR use in SEARCH than AND use in SEARCH. Once you have gone through the below examples, you will understand why it is so.

OR Use in the SEARCH Function

Example 1

Assume cell C2 contains the word “train timing”

I want to check whether cell C2 contains any of the text train, bus, flight, tram, or metro.

If any of the above texts are present in cell C2, return “Transportation” else “Unknown Mode of Transportation”!

=if(
     sum(
        iferror(
           ArrayFormula(
              search({"Train";"Flight";"Bus";"Tram";"Metro"},C2)
           )
        )
     )>0,
     "Transportation","Unknown Mode of Transportation"
)

I will give you one more example, and in that, I’ll try to explain the formula in detail.

Example 2 (Step by Step Approach)

You are learning the OR part in the AND, OR use in the SEARCH function in Google Sheets.

In cell B2, I have a country name within a sentence. I want to test whether it is a country that uses the EURO currency.

The value in cell B2 is as follows – The currency of Italy is.

We should know whether “Italy” matches in the list of member countries who are using the EURO currency.

So make a list of member countries who are using the EURO currency as a list in D2:D.

Search_for and Text_to_Search

Here the text to search is the string in cell B2.

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

We will search the 19 countries (the countries who are using the EURO currency) in the range D2:D20 as below.

=ArrayFormula(
     search(D2:D20,B2)
)
Example to AND, OR Use in SEARCH Function

Note:- We can specify/hardcode the range D2:D20 as below too.

{"Austria";"Belgium";"Cyprus";"Estonia";"Finland";"France";"Germany";"Greece";"Ireland";"Italy";"Latvia";"Lithuania";"Luxembourg";"Malta";"the Netherlands";"Portugal";"Slovakia";"Slovenia";"Spain"}

I have used the ArrayFormula function because the search_for is an array.

The SEARCH function returns the position of the serch_for (D2:D20) in text_to_search (B2) by counting the characters in the cell B2.

If the total of the above formula result is greater than zero, then return “Euro”, else “Unknown”.

Before summing the above result, we should eliminate the #VALUE! errors. Then only the SUM would work. We can use IFERROR for that.

=if(
     sum(
        iferror(
           ArrayFormula(
              search(D2:D20,B2)
           )
        )
     )>0,
     "Euro","Unknown"
)

Change the text in cell B2 to The currency of Japan is. The formula would return “Unknown”.

You have learned the OR use in SEARCH. Now in our AND, OR Use in SEARCH Function in Google Sheets, the next part is the AND use.

AND Use in the SEARCH Function

In AND use in the SEARCH function, we want to check the presence of multiple keywords in the text_to_search part of the formula.

For example, the value in cell D1 is “diesel injection pump”. I want to test the presence of the words “diesel” and “pump” in this string.

First we will search “diesel” in D1. The formula for the same is;

=ArrayFormula(
     IFERROR(
        search("diesel",D2)
     )
)

Now let’s search “pump” using the following SEARCH formula.

=ArrayFormula(
     IFERROR(
        search("pump",D2)
     )
)

If the count of both the result is 2, that means both the words are present.

=count(
     ArrayFormula(IFERROR(search("diesel",D2))),
     ArrayFormula(IFERROR(search("pump",D2)))
)

Note:-

Most of the time, in an array formula similar to the one above, no need to depend on more than one ArrayFormula function.

You can remove the inner Array Formulas and corresponding open/clause brackets and then wrap the entire formula with a single ArrayFormula.

The above are examples of the use of logical AND, OR in SEARCH function in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.