lookup with multiple conditions and for rows-CodePudding

Hello sages from StackOverflow,

I'm in search of a formula that can relate 3 diferent conditions, I tried using some IF statemets with the TEXTJOIN formula but I find myself lost in the way,

I got a data base just like this (image below), just a much bigger one, I want to search for a key like MCAA01 and obtain the doc's that have in front of it a "NO" all in one cell, like if you use the formula TEXTJOIN("/",...

My problem is that I cannot find a way to relate the whole column of the doc's with the key,

I tried something like TEXTJOIN("/",TRUE,IF(2ndIMAGE!A2=1stIMAGE!B1,IF(B2="no",1stIMAGE!A2,""),""))

This does give a result but it's just 1 thing, not whole answer

please sages of StackOverflow, you're my only hope. Thank you!

CodePudding user response：

You need FILTER() then TEXTJOIN().

=TEXTJOIN("/",TRUE,FILTER($A$2:$A$4,FILTER($B$2:$H$4,$B$1:$H$1=$B8)="No",""))

If your version of excel supports LAMBDA() function then you can try below formula and you do not need to drag the formula to each cell. It will spill results automatically.

=BYROW(B8:B14,LAMBDA(a,TEXTJOIN("/",TRUE,FILTER(A2:A4,FILTER(B2:H4,B1:H1=a)="No",""))))