1

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.

  • 1
    Hey, your test sheet is not public, can you change the sharing settings (view) – pjmg Oct 19 at 12:20
  • Have you tried any of the solutions offered? Did any of them work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Oct 25 at 19:04
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.

formatting and empty rows transfer through the filter function

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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