0

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.

2
  • 1
    Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Sep 12, 2023 at 4:04
  • Charlie you now have two answers using two different assumptions. The goal of this site is to have great questions and answers. Please help us to help you and people who come after. edit your question based on the suggestions and provide feedback on the answers so we have clarity about what you wanted. 👍
    – Blindspots
    Commented Sep 12, 2023 at 14:28

2 Answers 2

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.

1
  • 1
    The OP needs to post an MRE. Commented Sep 12, 2023 at 4:05
0

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: COUNTIFFILTERSEARCH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.