Issue with Textjoin, multiple criteria. IF/AND function

Copper Contributor

Hi

 

I have attached an example workbook with some example data.

 

My goal is for someone to type in the product and the company category and be given a list of companies that match those two criteria.

 

It needs to be exact. I don't want it to show companies that have the right category but not the product. It needs to be both.

 

I have a working TEXTJOIN function in the example work book that successfully works at listing the companies that match the desired product. But I cannot find a way to integrate an IF(AND or INDEX/MATCH to also include the second required field (Category K15) as an 'AND' function.

 

Let me know if you need more sample data.

 

Any help would be appreciated.

 

 

 

 

13 Replies

@MrRAMMount 

Transpose the category table to B12:E13.

{=TEXTJOIN(", ",TRUE;IF(MMULT({1;1;1;1},(B6:E9=K14)*(B13:E13=K15)),B5:E5,""))}

I hope ; is correct column separator. IF not you have to change it. 

@Detlef Lewin 

 

Hi

 

Thanks for the quick response. I am happy to reorganise the data however will work. I am unsure which orientation you mean when transposing the company/category to B12-E13.

 

You mean along row 12 the company 

and along row 13 the category?

 

If the company labels are already along row 5, do we need to add them again?

 

How would your suggested formula change if I add the categories above the Company names (row 4)? I attached the updated workbook to show what I mean.

 

Thanks

Hi

I tested it and it works great on the sample workbook I attached.

However I cannot get it to work on my main workbook. On my main workbook I have different worksheets with the different data. Is there an issue with the functions you suggested when taking data from different sheets?

Thanks

@MrRAMMount 

Different worksheet it not a problem. Most likely you have more than four companies. Then you have to adjust this part: {1.1.1.1} - which is for four companies (or four columns).

I have 30 companies (30 columns) and thousands of products.

There are only around 5 categories spread over the companies.
Does the position of the Categories matter. I can't understand why it works every time in the example workbook, but I get #value everytime I try in my main one.

=TEXTJOIN(", ",TRUE,IF(MMULT({1,1,1,1},(A37:AD584=A4)*(A35:AD35=C2)),A36:AD36,""))

This is what I am trying to do. Assuming the data is where the formula says, is there any reason this shouldn't work? I tried it with 30 x ,1 but that didn't help.

Thanks again
I also tried it on my main worksheet (copy pasted all data into one work sheet to test).

I selected the first 4 columns so the {1,1,1,1} should be correct and it still returns#VALUE!

Is there a formatting issue with these functions? Is there anything else I need ensure is a certain way to use MMULT?

Thanks
I have tried it selecting only the first 4 Companies and 4 rows of products and it works fine.

How do I modify the function to allow for 30 columns and between 5 and 550 rows?

@MrRAMMount 

Sorry, my fault.

{1;1;1;1} is for four product rows. More rows more 1's.

 

If you can change you whole layout it would be easier with Power Query.

One table: Company - Product

And the other table: Company - Category

And one table for the search criteria.

 

Join the first two tables and filter with the third table.

 

@Detlef Lewin 

 

Hi

 

Thanks again for your help. 

 

I am not very savvy with more complex functions in excel like MMULT and power querys.

 

Could you suggest how the scaled up formula might look?

 

At the minute I have about 30 columns. Each column 1st row is a Company, each column second row is that company's category and each company has between 5 and 550 products in that column.

 

I can put the search criteria anywhere.

 

Considering this, is there a way to still use the formula you suggested before?

 

Thanks

 

Hi

 

I seem to have sorted it out. It works mostly as I intend.

 

Here is a link to an example workbook where it functions properly.

 

https://docs.google.com/spreadsheets/d/1AKp5Ajg77tpwvCYECbnkg9fSls6SrdbYANrIA305trg/edit#gid=0

 

My last question is what can I add to the working formula in the doc above that will not return any values if either of the search criteria are empty?

 

Thanks

Hi

I have sorted it out finally now. I added an IF(OR with ISEMPTY to only start the TEXTJOIN function if both search cells are not empty.

=IF(OR(ISBLANK(B5),ISBLANK($C$12))," ",TEXTJOIN(", ",TRUE,IF(($A$52:$AD$604=B5)*($A$50:$AD$50=$C$12),$A$51:$AD$51,"")))

Thanks for the help.

Jon

 


@MrRAMMount wrote:
Thanks for the help.

Jon

You're welcom Jon.