Get Company names based on Product names

Hi friends,

I have some data with Suppliers, Buyers names for some products. If I want to know particular product supplier (or) buyer names, what kind of formula I can use. Please see attachment.


As per attached sheet.

H1 is Products List Option, H3 is option to choose Buyer (or) Supplier, I want total company names based on H1, H3 options, Names result from H7.

If I change options in H1, H3, names will come accordingly

Thank you.

This should work:
Array formula, Ctrl+Shift+Enter instead of regular Enter:

=IFERROR(INDEX($A$3:$A$17, SMALL(IF(ISNUMBER((SEARCH($H$3, $B$3:$D$17))*(SEARCH($H$1, $B$2:$D$2))), ROW($A$3:$A$17)-MIN(ROW($A$3:$A$17))+1, ""), ROW(A1))),"")

=iferror(index($a$1:$a$17,aggregate(15,6,row($a$3:$a$17)/isnumber(search(h$3,index($b$3:$d$17,,match(h$1,$b$2:$d$2,)))),rows($a$7:a7))),"")

Excellent work!!! PaulM100 & tim201110,

Thank you.

Hi tim201110,

One small update please, If I want dont show one company "Pan Pacific" in any search, then where I can put that name in formula and how ?, please provide me this update.

Thank you,

Hi JohnTopley,

Actual problem is our company purchase material from 1 of these 17 customers, and send selling quotation emails to other 16 customers automatically. If we keep that 1 customer in list, then seller will receive buying quotation . I will know that 1 customer name at the time emailing. So, every time we can't remove or change range in formula. Is there any other solution.

Thank you.

Try

=IFERROR(INDEX($A$1:$A$17,AGGREGATE(15,6,ROW($A$3:$A$17)/(($A$3:$A$17<>"Pan Pacific")*(ISNUMBER(SEARCH(H$3,INDEX($B$3:$D$17,,MATCH(H$1,$B$2:$D$2,)))))),ROWS($A$7:A7))),"")

Thank you very much John Topley, Its working!!!