How to definitely use column names in Google Sheet Query

Question

query function doesn't let you use column names; you have instead to use letters if you refer to a cell range or ColN if you refer to an array. This is very annoying, most of all when you alter the queried table adding, deleting or exchanging columns.

I would like to use column names, like in a standard SQL query.

Answer 1

You can actually get around this by splitting the Query formula and using other formula's to automatically get the desired column names from a list.

For example if you have a table in range A1:E15 with headers "H1, H2, H3, H4, H5", and you'd like to only get columns H3 & H5:

1. Store the desired headers (H3 & H5) in another table/range as a list - lets say this range is G1:G2

2. Use MATCH formula along with TextJoin formula to generate an concatenated string like Col3, Col5

   =TextJoin(", ",TRUE,ArrayFormula(IFERROR("Col"&MATCH(G1:G6,$A$1:$E$1,0),"")))
   

   • Lets say this was in cell H1

3. You can refer to this cell in your Query formula like below

   =QUERY({A1:E20},"SELECT "&H1&" WHERE Col2='w'")
   

You can see it in action in below screenshot: