Sorry for the bad title. Have no idea how I could describe what I want to do in a single sentence.
Hey guys, 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:
Sheet 1
| Color | Formula column |
|---|---|
| red | =??? |
| green | =??? |
| blue | =??? |
Sheet 2 (imported from another file with 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
Sheet 1 (expected)
| 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 AppScript but since the lookup data in sheet two has more than 1000 rows it always ends up in a exectuion timeout.
Can someone point me in the right direction?
CodePudding user response：
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"))))
- Replace [SHEET 2 SPREADSHEET ID] in the formula with the relevant sheet ID
CodePudding user response：
try:
=INDEX(IFNA(VLOOKUP(A7:A10, LAMBDA(x, {OFFSET(x, 1,, 9^9, 1 ),
BYROW(IF(OFFSET(x, 1, 3, 9^9, 4)*1=1, OFFSET(x,, 3, 1, 4), ),
LAMBDA(y, TEXTJOIN(", ", 1, y)))})(A1:G5), 2, )))
