How can i select columns by name in a google sheet QUERY?-CodePudding

I am trying to QUERY specific columns from a google sheet. I can do it using the columns as a number, however, if a new column is added, the number of the column changes.

(https://i.stack.imgur.com/0FU4V.png)

For example: it is working with this function: =QUERY("name of the sheet!A1:BH1770", "SELECT Col1, Col3 where Col1 contains '"&$F2&"'", 0)

When i try something like this function, it is not working: =QUERY(ImportRange!A1:BH1900,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Auditor",ImportRange!A1:BH1,0),4),1,"")& "&SUBSTITUTE(ADDRESS(1,MATCH("RANK(",ImportRange!A1:BH1,0),4),1,"")&", WHERE "&SUBSTITUTE(ADDRESS(1,MATCH("Auditor",ImportRange!A1:BH1,0),4),1,"") contains'"&$F5&"'", 0))

Thanks in advance.

CodePudding user response：

You can try by referring by wrapping the range in curly brackets and referring by column number with MATCH:

=QUERY({ImportRange!A1:BH1900},"SELECT Col"&MATCH("Auditor",ImportRange!A1:BH1,0)&", Col"&MATCH("Rank",ImportRange!A1:BH1,0)&" WHERE Col"&MATCH("Auditor",ImportRange!A1:BH1,0)&" contains '"&$F5&"'", 0)