Column LOOKUP Dynamic Query (Google Sheets)

user_7283061 0
Viewed 58

Trying 'query' for the first time in google sheets. Need some assistance. Thanks in advance.

I have a google sheet with two tabs "sheet 1" and "sheet 2"

Sheet 1 Structure:

enter image description here

Sheet 2 Structure:

enter image description here

I am trying to create a dynamic query, where if Entity = Person "x" and Value = A or B or C, the Des number (from sheet2) is populated in Sheet 1 in yellow cells

So far I have tried these queries and for some reason it throws a "#N/A" error

  1. QUERY({Sheet2!$A$1:$AC$10}, "SELECT Col1,Col"&MATCH(B1,Sheet2!$A$1:$AC$1,0))

  2. LOOKUP($B$1,QUERY({Sheet2!$A$1:$AC$10}, "SELECT Col1, Col"&MATCH("L",Sheet2!$A$1:$AC$1,0)))

Can someone point me in right direction with this query?

google-sheets-formula google-sheets
1 Answers

It's bit more complex as you have first to unpivot Sheet2, then apply query, finally join the results in one cell.

=iferror(textjoin(char(10),,query(arrayformula(split(flatten(Sheet2!$A$2:$A$4&"|"&Sheet2!$B$1:$F$1&"|"&Sheet2!$B$2:$F$4),"|")),"select Col2 where Col1='"&$A2&"' and Col3='"&B$1&"' ")))

adjust Sheet2!$A$2:$A$4&"|"&Sheet2!$B$1:$F$1&"|"&Sheet2!$B$2:$F$4as necessary.

Explanation

Sheet2 is like enter image description here

To unpivot Sheet2, try

=arrayformula(split(flatten(Sheet2!$A$2:$A$4&"|"&Sheet2!$B$1:$F$1&"|"&Sheet2!$B$2:$F$4),"|"))

enter image description here

then apply your query

query(_______________,"select Col2 where Col1='"&$A2&"' and Col3='"&B$1&"' ")

and finally join the results enter image description here

user_150193800
Related