Issue with TEXTJOIN, multiple criteria. IF/AND function

[MrRAMMounts]

Hi

I am unsure how to attach an example workbook for my query.

As you can see from the image above, I have Company, which has two data sets associated with it - Product and Category.

I am looking to allow someone to input the product and category then be given a , separated 'list' of companies that meet both criteria.

As you can see from the example I have used a MMULT function with TEXTJOIN (someone's suggestion from another site), however my actual workbook has currently 30 companies and between 5 and 550 rows of products.

The current formula does not work in my main workbook.

I can get TEXTJOIN to work for just the products-

=TEXTJOIN(", ",TRUE,IF(AllProducts!A3:AD551=Sheet2!A4,AllProducts!A2:AD2,""))

-but I cannot figure out how to integrate a second criteria to this.

I have tried IF(AND with an INDEX/MATCH as the second logical argument but I got confused.

Any help is greatly appreciated.

https://i.imgur.com/ZdQdDz1.png

Regards

 

[Eric W]

Presumably each company column has each product in it at most once, otherwise your formula would return the company name multiple times. You didn't say why the current formula doesn't work. Based on the sample sheet, it should if the ranges are adjusted properly. Nevertheless, your new formula should also work, something like:

=TEXTJOIN(", ",TRUE,IF((AllProducts!A3:AD551=Sheet2!A4)*(AllProducts!A600:AD600=Sheet2!A5)),AllProducts!A2:AD2,""))

confirmed with Control+Shift+Enter. This assumes your category row is 600, and the category you're looking for is in A5. This is untested since I don't know what your current layout looks like, but give it a try.

 

[MrRAMMounts]

Presumably each company column has each product in it at most once, otherwise your formula would return the company name multiple times. You didn't say why the current formula doesn't work. Based on the sample sheet, it should if the ranges are adjusted properly. Nevertheless, your new formula should also work, something like:=TEXTJOIN(", ",TRUE,IF((AllProducts!A3:AD551=Sheet2!A4)*(AllProducts!A600:AD600=Sheet2!A5)),AllProducts!A2:AD2,""))confirmed with Control+Shift+Enter. This assumes your category row is 600, and the category you're looking for is in A5. This is untested since I don't know what your current layout looks like, but give it a try.

Hi EricThanks for your response. I have actually removed all duplicates from the list, so each column contains 'Compan'y > 'Company's respective category' and a list of unique products that company stocks.Here is a link to an example workbook with some example data.https://docs.google.com/spreadsheet...nkg9fSls6SrdbYANrIA305trg/edit?usp=sharingYou can see two formula in the workbook.The first was suggested by someone and uses MMULT (I don't understand how it works) to somehow calculate correctly what I need to do. The only caveat is that thisonly works with a 4x4 array for this example data. I don't know how to scale up this (or if it can be scaled up) to accommodate 30 columns with between 5 and 550 rows of products in each (my actualy main workbook).The second is the TEXTJOIN function. This currently works in all cases, but only for 1 search criteria. This works if someone gives just the product. It successfully TEXTJOINs all the relevantcompanies that contain that product.My goal is to use one of these formula to add an additional 'AND' into the argument.IF the searched array returns a match for Criteria 1 (product) AND Criteria 2 (category) then return a TEXTJOINed list.Is that something that could be done? I don't know if the second AND would need to use an INDEX/MATCH function or something else.If you are certain it can't be done with the data organised like this I am happy to re organise it in a different suggested way.Thanks for your time.

 

[MrRAMMounts]

I currently have a duplicate thread open on EXCEL Forum. Please see this link for details.

https://www.excelforum.com/excel-general/1294484-using-textjoin-with-if-and-functionality.html

 

[MrRAMMounts]

Presumably each company column has each product in it at most once, otherwise your formula would return the company name multiple times. You didn't say why the current formula doesn't work. Based on the sample sheet, it should if the ranges are adjusted properly. Nevertheless, your new formula should also work, something like:

=TEXTJOIN(", ",TRUE,IF((AllProducts!A3:AD551=Sheet2!A4)*(AllProducts!A600:AD600=Sheet2!A5)),AllProducts!A2:AD2,""))

confirmed with Control+Shift+Enter. This assumes your category row is 600, and the category you're looking for is in A5. This is untested since I don't know what your current layout looks like, but give it a try.

Eric I have tried this method and it works!!

Thanks so much. I would really appreciate it if you could briefly explain why multiplying together two arguments where the resulting values are words works in this way?

Thanks again

 

[Eric W]

First, the MMULT formula would have worked. Probably the biggest thing confusing you was the array constant {1,1,1,1}. In order to scale up the formula to handle 30 columns, you'd need an array constant with 30 1's in it, not just 4. For a lot of columns, or a variable number of columns, there are other methods than just typing a lot of 1's. But I'll skip the long explanation since you didn't go that route, and explaining matrix multiplication (MMULT) is probably beyond the scope of this thread.

As far as the newest formula, it also involves multiplying arrays, but it's a bit simpler. Here's the basic format of the formula:

=TEXTJOIN(", ",TRUE,IF((B6:E9=K14)*(B13:E13=K15),B5:E5,""))

The (B6:E9=K14) part of it generates a 2-D array. K15 is a word, but the result of the comparison is a Boolean variable, in other words a TRUE or FALSE value. For ease, I'm going to use 1 for TRUE and 0 for FALSE. So when it calculates, it compares every value in B6:E9 againt K14 and returns a TRUE or FALSE. The resulting array will have the same dimensions as the range you started with, so it will look something like:

{0,0,1,0;
1,0,0,0;
0,0,0,0;
0,0,0,1}

Commas indicating move horizontally, semicolons indicating go to next row. So for this example D6, B7, and E9 would match K14.

The (B13:E13=K15) part works the same way, but on just one row, so you'd get something like:

{1,0,1,0}

where B13 and D13 match. The last part of it is what happens when you multiply (*) the two arrays. In this case, the number of columns of each array must be the same. You take the 1 from the 1st column of the second array, and multiply it by each row of the first column of the first array. Take the 0 from the 2nd column of the second array and multiply it by each row of the 2nd column of the 1st array. And so on. You end up with:

{0,0,1,0;
1,0,0,0;
0,0,0,0;
0,0,0,0}

Then the IF looks at all 16 elements of the array, finds a 1 in the 1st and 3rd columns, and returns the corresponding value from B5:E5. This is why I originally asked if there were duplicates in any column. If there were, you would have multiple 1's in a column, so you'd get the column heading multiple times. The MMULT formula worked a bit differently and compressed the final array to a single row, so that wasn't an issue. But if there are no duplicates, then this is a simpler formula.

There are lots of variations on this. You might look up some links on SUMPRODUCT. It also allows you to multiply ranges together in a very similar fashion which might be enlightening.

 

[MrRAMMounts]

Eric

Thanks for the explanation. That makes sense now I was a little confused by the matrix calculations as had never really extended my Excel knowledge that far. However I really appreciate you taking the time to go through it!

I know who to ask next time! Thanks again for the help.

Regards

Jonathan