Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Is there a way to return an array of values using filter function based on two "include" conditions in filter function in Excel / Google Sheets?

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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"))

enter image description here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading