Oct 29 2019 07:24 AM
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.
Oct 29 2019 07:55 AM
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.
Oct 29 2019 08:02 AM
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
Oct 29 2019 08:23 AM
Oct 29 2019 08:29 AM
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).
Oct 29 2019 08:33 AM
Oct 29 2019 08:37 AM
Oct 29 2019 08:42 AM
Oct 29 2019 08:52 AM
Oct 29 2019 10:27 AM
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.
Oct 30 2019 02:13 AM
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
Oct 30 2019 04:55 AM
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
Oct 30 2019 05:25 AM
Oct 30 2019 05:48 AM