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:

Sheet 2 Structure:

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?

CodePudding user response：

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

To unpivot Sheet2, try

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

then apply your query

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

and finally join the results