'How can I make queried column dynamic, as per what's entered in the column header to be populated?

Right now, I can populate a sheet with data queried and that gets the destination column header name dynamically, using this:

=query(
Scenarios!A5:FQ,
"select "&REGEXEXTRACT(ADDRESS(5,MATCH(B2,Scenarios!A5:5,0)),"[A-Z]+")&"
 where "&REGEXEXTRACT(ADDRESS(5,MATCH("PO #",Scenarios!A5:5,0)),"[A-Z]+")&" = 
 '"&$B$13&"'",0)

How can I use a similar approach when in this case?

=vlookup(AB9,Scenarios!A:BL,64,FALSE)

Here is an example of the data

Thank you!

arraysgoogle-sheetsgoogle-sheets-formulavlookupgoogle-query-language


Solution 1:[1]

see:

=FILTER(A7:C; REGEXMATCH(A7:C7; "Region|Rep"))

enter image description here

if order of columns matters:

=QUERY(FILTER(A7:C; REGEXMATCH(A7:C7; "Region|Rep")); "select Col2,Col1")

enter image description here

or you can try:

=INDEX(VLOOKUP(ROW(A7:A); {ROW(A7:A)\ B7:C}; {3\ 2}; 0))

enter image description here

or like:

=INDEX(VLOOKUP(ROW(A7:A); {ROW(A7:A)\ B7:C}; MATCH({"Rep"\ "Region"}; A7:C7; 0); 0))

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0

Related Questions