Search code examples
google-sheets-formula

Google Sheets Query - use a reference cell to select which columns to select in a query

I would like for a Google Sheets Query to choose which columns to select based on a cell reference. "select X" for example where the column header equals a cell that I reference. This is different from selecting which rows of a column to display. I want the Colx to be based on a cell reference.

Specifically, the data has different column headers with dates. I want the query to select the column that matches a particular date or any of the headers I may have in a drop down menu.

I used this format but it did not work

  • =query('Schedules'!A:N,"select A,B,C,D,E,K,H,J,L,M, '"&A2&"' WHERE N=1 and K !='HR(PD)' ORDER BY B,K",1)

This is the cell reference to the column I am looking for: '"&A2&"' in A2 I have the letter of the column I want to use

Solution

  • Your current formula maybe a bit spotty with the quotes placement. You can see a working sample here:

    =query(A:D,"Select A,"&F1&" Where A is not null")

    enter image description here

    based on header match

    =query({A:D},"Select Col1, Col"&xmatch(F1,A1:D1)&" Where Col1 is not null")

    enter image description here