I am looking for a formula based solution to map information from one sheet that is imported using IMPORTRANGE
to another sheet (what a bad explanation). But an example might be easier to understand - the following two sheets are given:
Color | Formula column |
---|---|
red | =??? |
green | =??? |
blue | =??? |
IMPORTRANGE
)color | details1 | details2 | peter | susan | john | christin |
---|---|---|---|---|---|---|
red | not important | not important either | 1 | 0 | 0 | 1 |
green | not important | not important either | 1 | 1 | 0 | 0 |
And this is what I want the result should look like
Color | Formula column |
---|---|
red | peter, christin |
green | peter, susan |
blue | nobody |
Sadly, I am not really good with the formula stuff. I've created an Apps Script but since the lookup data in sheet two has more than 1000 rows it always ends up in a exectuion timeout.
use:
=BYROW(A2:A,LAMBDA(ax,if(ax="",,IFERROR(JOIN(", ",query({LAMBDA(bez,QUERY(INDEX(SPLIT(FLATTEN({importrange(bez,"Sheet2!A2:A")&"|"&importrange(bez,"Sheet2!D1:G1")&"|"&importrange(bez,"Sheet2!D2:G")}),"|",0,0)),"Select * Where Col1!='' AND Col3=1"))("[SHEET 2 SPREADSHEET ID]")},"select Col2 Where Col1='"&ax&"'")),"nobody"))))