How to filter combined search outputs with OR logic

Question

I have a working Google Sheets filter formula that combines different searches of the same range.

In the formula below, I'm searching column AA in the REFS worksheet.

I enter search terms into any or all cells AA1 - AD1 in the current sheet.

This formula searches for each of these criteria and displays rows matching ALL search terms.

=IFNA(filter(REFS!$A$3:$AA,
IF(AA1<>"",SEARCH(AA1,REFS!AA3:AA),"")+
IF(AB1<>"",SEARCH(AB1,REFS!AA3:AA),"")+
IF(AC1<>"",SEARCH(AC1,REFS!AA3:AA),"")+
IF(AD1<>"",SEARCH(AD1,REFS!AA3:AA),"")),"")

This works fine whether I supply one or multiple criteria.

However, this is an ALL search. It only displays rows matching all search criteria.

I need an OR version of this to display rows that match any search criteria.

Answer 1

To search each row in REFS!A3:AA for partial and case-insensitive matches of any of the text strings in AA1:AD1, use regexmatch(), like this:

=ifna( 
  filter( 
    REFS!A3:AA, 
    regexmatch( 
      REFS!AA3:AA, 
      "(?i)" & textjoin("|", true, AA1:AD1) 
    ) 
  ) 
)

Note that this only works as long as none of the text strings in AA1:AD1 contain regex special characters.

Answer 2

Not Search

SEARCH locates the position of a text string within another text string, not a value in a range.

Example:

=SEARCH("bob", "jimbob") 
=4

Solution

To FILTER a range based on a list of values use:

=FILTER(range, COUNTIF(list, column))

For example:

=FILTER(REFS!A3:AA, 
   COUNTIF(AA1:AD1, REFS!AA3:AA)) 

See: COUNTIF FILTER SEARCH