I have a matrix of shops and raw materials as shown below
Item Name Shop A Shop B Shop C Shop C Shop D Shop E
AISI 4340 Yes Yes Yes Yes
17-4PH Yes Yes
AISI 4140
SS 304 Yes
SS 301 Yes Yes Yes
AISI 1010 Yes Yes Yes
AISI 1020 Yes Yes Yes Yes Yes Yes
AA 7075 T6 Yes Yes Yes Yes Yes
AA 7075 T351 Yes
AA 6061 T6 Yes Yes Yes Yes Yes
"Yes" means raw material is available at that shop.
Now I am struggling with filter function to return an array of companies which have a given material. given material is mentioned in a separate cell and is one of the item names in the above matrix.
Google sheet link for my document here
I am trying to use formula
=transpose(FILTER(C3:H3;(C4:H13="Yes")*B4:B13=J3))
in cell J5 but it is giving me an error
I tried different approaches to get to the desired result without any success.
>Solution :
You may try:
=tocol(filter(C3:H3; filter(C4:H13;B4:B13=J3)="Yes"))
