Return to Answer

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)][1] [1]: https://webapps.stackexchange.com/a/135613/222777

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)

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

You need to replace the string A,D in the query with something like this :

=textjoin(",",true, switch(or((ArrayFormula(regexmatch(A1:A,"Adam")))),false,,true,char(64+COLUMN(A1))), switch(or((ArrayFormula(regexmatch(B1:B,"Adam")))),false,,true,char(64+COLUMN(B1))), switch(or((ArrayFormula(regexmatch(C1:C,"Adam")))),false,,true,char(64+COLUMN(C1))), switch(or((ArrayFormula(regexmatch(D1:D,"Adam")))),false,,true,char(64+COLUMN(D1))) ) 

Then use it in your query like this =query( range , "Select "& (the formula) & " Where [..]")

Credit to [this answer (how to get a column's address)][1] [1]: https://webapps.stackexchange.com/a/135613/222777

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)][1] [1]: https://webapps.stackexchange.com/a/135613/222777

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

You need to replace the string A,D in the query with formula like this :

=SUBSTITUTE( textjoin(",",true,ArrayFormula(ADDRESS(1, split( textjoin(",",true,{ switch(or((ArrayFormula(regexmatch(A1:A,"Adam")))),false,,true,COLUMN(A1)), switch(or((ArrayFormula(regexmatch(B1:B,"Adam")))),false,,true,COLUMN(B1)), switch(or((ArrayFormula(regexmatch(C1:C,"Adam")))),false,,true,COLUMN(C1)), switch(or((ArrayFormula(regexmatch(D1:D,"Adam")))),false,,true,COLUMN(D1)) }) ,",",true,true)   ,4))),1,) 

Then use it in your query like this =query( range , "Select "& (the formula) & " Where [..]")

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

You need to replace the string A,D in the query with something like this :

=textjoin(",",true, switch(or((ArrayFormula(regexmatch(A1:A,"Adam")))),false,,true,char(64+COLUMN(A1))), switch(or((ArrayFormula(regexmatch(B1:B,"Adam")))),false,,true,char(64+COLUMN(B1))), switch(or((ArrayFormula(regexmatch(C1:C,"Adam")))),false,,true,char(64+COLUMN(C1))), switch(or((ArrayFormula(regexmatch(D1:D,"Adam")))),false,,true,char(64+COLUMN(D1))) ) 

Then use it in your query like this =query( range , "Select "& (the formula) & " Where [..]")

Credit to [this answer (how to get a column's address)][1] [1]: https://webapps.stackexchange.com/a/135613/222777