Using Textjoin with IF(AND functionality

[MrRAMMounts]

Hi

I am trying to figure out how to use the Textjoin function, but with an IF(AND as part of the logical argument.

2019-10-28 16_39_57-Window.png

Above is a small extract of my data set. It is a set of companies with the list of components of ours that they sell (not sensitive info).

In another worksheet I have all the company names and their category (dealer, distributor, integrator etc) in 2 adjacent columns.

I am looking to create a table where if someone types in a product of ours (e.g. RAM-B-101) and types a category into a cell (e.g. Dealer) it will text join all the companies that are the selected category and have the selected product in their range.

=TEXTJOIN(", ",TRUE,IF(AllProducts!A2:AD550=Sheet2!A4,AllProducts!A1:AD1,""))

This works successfully to Textjoin the company names that have the selected product in their range.

However I cannot figure out how to insert an AND function to include matching the company category also.

=TEXTJOIN(", ",TRUE,IF(AND(AllProducts!A2:AD550=$A$4,INDEX(Categories!A2:A31,MATCH(C2,Categories!A2:B31,0))),AllProducts!A1:AD1,""))

This is what I have got to, but it doesn't work and I am hoping one of you guys could give me some advice.

Thanks in advance.

Jon


-edit

I have a duplicate question currently also posted in an additional Forum. Here is the link

https://www.mrexcel.com/forum/excel-...ml#post5363438