Using Query to select column based on the headers-CodePudding

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%"`

CodePudding user response：

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"

CodePudding user response：

You can define the following Named 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