Google Sheets – Search Text in All Columns of Multiple Sheets

google sheets

Given many texts to look for, I want to search all my sheets in all columns if there's at least one partial case insensitive match then return true (or 1 or the sheet's name or whatever) and false otherwhise…

enter image description here

I didnt go far, I tried the following which searches one col in the next sheet (just one, yet to make it search in all of them):

=IF(FIND(A1,sheet1!A2:A1000)>0,"true","false")

I'm getting:

Error
An array value could not be found.

All suggestions/solutions are welcome to solve my whole problem as explained at the start

Best Answer

You need search (case-insensitive) instead of find (case-sensitive). Also, a few wrappers around it:

=max(arrayformula(iferror(search(A1, sheet1!A1:A1000)))) > 0
  1. iferror ensures empty output instead of #N/A when the cell is empty or text cannot be found
  2. arrayformula specifies that we work with an array
  3. max returns the maximum (so it will be positive if at least one cell matched)
  4. Comparison >0 will return TRUE or FALSE; no if statement needed.

The above works for multiple columns at once: you can use sheet1!A1:Z1000 and so on.

The results from multiple sheets can be pooled together by addition prior to comparison: 

=max(arrayformula(iferror(search(A1, sheet1!A1:Z1000)))) + max(arrayformula(iferror(search(A1, sheet2!A1:Z1000)))) > 0
Related Topic