Using Query to select column based on the headers

Question

I have a dynamic table where columns are moved every now and then. Therefore, I would like to reference my column name in my query. Unfortunately I do not know so well and the internet raises more questions.

My formula looks like this:

`=query('X Source'!A:AP, "select D, E, AA, AM, X, A where "&if(month(now())=1,"(month(A)<11)","(month(A)  <=month(now())-2)")&" and (V like 'C & G' or V like 'SAS' or V like 'SXS D' or V like 'DIR') Order By A    desc")

D = Cinter
E = Cluster
AA = Creation Date
AM = Change Ow
X = Title
A = Date`

Do you have any idea ? I would like not to write a script.




I have already tried with the function filter to bypass but there I get no further because of the filtering after month. 

`={FILTER('X Source'!AA:AA, 'X Source'!V:V="SAS",'X Source'!X:X<>"%BY SB%",'X Source'!X:X<>"%SB ONLY%",     month('X Source'! AA:AA)=month(today())-1);FILTER('X Source'!AA:AA,'X Source'!V:V="SXS D",'X     
Source'!X:X<>"%BY SB%",'X Source'!X:X<>"%SB ONLY%"`

Answer 1

You could use MATCH to find the numbers of columns, and grab your range in curly brackes so you can refer them as Col1,Col2,Col3 instead of A,B,C

Just to make it more dinamic and you could change your range, I wrapped it in LAMBDA. With the headers I matched all your values and joined them with comma. 'date' only matched that colum. Column V I had my doubts about if it was a mistake when you said which columns was located at which headers. Please change "Title" in ""Col"&MATCH("Title",INDEX(range,1),0)" to the actual desired header title (that now is in V column) so it matches correctly:

=LAMBDA(range,
LAMBDA(headers,date,title,query({range}, "select "&headers&" where "&if(month(now())=1,"(month("&date&")<11)","(month("&date&")  <=month(now())-2)")&" and ("&title&" like 'C & G' or "&title&" like 'SAS' or "&title&" like 'SXS D' or "&title&" like 'DIR') Order By "&date&" desc"))(
  JOIN(",",INDEX("Col"&MATCH({"Cinter","Cluster","Creation Date","Change Ow","Title","Date"},INDEX(range,1),0))),
  "Col"&MATCH("Date",INDEX(range,1),0),
  "Col"&MATCH("Title",INDEX(range,1),0)
))
('X Source'!A:AP)

In my dummy example with random columns, the inside part of the query would look like this:

"select Col7,Col3,Col15,Col20,Col11,Col12 where (month(Col12)<11) and (Col11 like 'C & G' or Col11 like 'SAS' or Col11 like 'SXS D' or Col11 like 'DIR') Order By Col12 desc"

---

UPDATE with other Locale

=LAMBDA(range; LAMBDA(headers;date;pl;query({range}; "select "&headers&" where "&if(month(now())=1;"(month("&date&")<11)";"(month("&date&") <=month(now())-2)")&" and ("&pl&" like 'C & G' or "&pl&" like 'SAS' or "&pl&" like 'SXS D' or "&pl&" like 'DIR') Order By "&date&" desc";1))( JOIN(",";INDEX("Col"&MATCH({"Cinter";"Cluster ";"Creation Date";"Change Owner";"Title";"Date"};INDEX(range;1);0))); "Col"&MATCH("Date";INDEX(range;1);0); "Col"&MATCH("PL";INDEX(range;1);0) )) ('X Source'!A1:AS)

Answer 2

You can define the following Named Function. (Data > Named functions > Add new function)

Name:

BETTERQUERY(range, better_query, headers)

Definition

=QUERY({range},IF(IFERROR(SPLIT(better_query,"`")=better_query,1),better_query,
REGEXREPLACE(REDUCE(better_query,REGEXEXTRACT(better_query,REGEXREPLACE(
REGEXREPLACE(better_query,"([()\[\]{}|\\^$.+*?])","\\$1"),"`(.*?)`","`($1)`")),
LAMBDA(acc,cur,SUBSTITUTE(acc,cur,IFNA("Col"&MATCH(cur,INDEX(range,1),0),cur)))),
"`(Col\d+)`","$1")),headers)

---

And then use it like this:

=BETTERQUERY('X Source'!A:AP',"select `Cinter`, `Cluster`, `Creation date` ...",1)

For more information on how this works see How to Use Column Names in QUERY

Answer 3

Here's my formula based on this post which is much shorter:

=QUERY(INDIRECT(A4&"!A1:Z"),"SELECT "&SUBSTITUTE(ADDRESS(1, MATCH(A5,INDIRECT(A4&"!A1:Z1"),0), 4),1,""))

• Put sheet name into A1
• Put header name (first row) into A2
• It will also include the header name in your query, if you want to remove it modify the first INDIRECT function and set it to INDIRECT(A4&"!A2:Z")

You can also add this function into a named function like this for ease use in other formulas: named function version for google sheets