Select All Columns Where Cell In Columns Contain A Word - Google Sheets

Question

I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column.

You can see a test sheet here: https://docs.google.com/spreadsheets/d/1aP7sDTf0xlxQFK51wiswiXcIMKvUNL_3hpi0qhDCvx4/edit#gid=0

For this example, I would like a query that would scan Sheet1 and select all columns that contain the words "Adam". This would select Columns A-D.

Any help would be appreciated.

Thanks.

Answer 1

In this case I believe the FILTER function would be a much better fit than the QUERY (which becomes quite convoluted).

Please try the following formula:

=FILTER(A1:I,A1:I1="Adam")

Pro Tip

Two significant differences of FILTER vs QUERY is that using the FILTER function you get to "transfer/preserve" both the numbers' original formatting as well as the empty rows.

Answer 2

Disclaimer : This is maybe not the most efficient way to do it, but more something like an intuition on how to do it.

Last try :

=textjoin(",",true,
ArrayFormula(char(64+unique(transpose((split(textjoin(",",true,
ArrayFormula(if(REGEXMATCH(A1:Z100,"Adam"),COLUMN(A1:Z100),))),",")))
))
))

---

Use this formula in your query like this :
=query( range , "Select "& (the formula) & " Where [..]")

---

Credit to this answer (how to get a column's address)