Transpose with 2 filters

[FuNeS13]

Okay I have a sheet that gets me a list of components based on the parent material, and the formula for that works great...

=TEXTJOIN(", ",,TRANSPOSE(FILTER(UniqueBOMs!A:A,$A2=UniqueBOMs!B:B)))

The thing that I need now is to add another filter to the result, which is check is $C$1=UniqueBOMs!F:F... my original idea was to try to do it as if I was looking 2 or more values in an Xlookup... Something like this:

=TEXTJOIN(", ",,TRANSPOSE(FILTER(UniqueBOMs!A:A,$A2=UniqueBOMs!B:B&$C$1=UniqueBOMs!F:F)))

but it doesn't work, it returns a blank cell (I know it should return 3 components for the 1st parent material)...

anyone know if what I'm trying to do is possible with a formula?

 

[Eric W]

Try:

=TEXTJOIN(", ",,TRANSPOSE(FILTER(UniqueBOMs!A:A,($A2=UniqueBOMs!B:B)*($C$1=UniqueBOMs!F:F))))

I'd recommend against using whole column references, it can make your sheet sluggish.

 

[FuNeS13]

oh yeah!!! that worked!!!! thank you Eric!!!

 

[Eric W]

Happy to help!