Forum Discussion

MrRAMMount's avatar
MrRAMMount
Copper Contributor
Oct 29, 2019

Issue with Textjoin, multiple criteria. IF/AND function

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.

 

 

 

 

TEXTJOIN

13 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Oct 29, 2019

    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. 

    • MrRAMMount's avatar
      MrRAMMount
      Copper Contributor
      Oct 29, 2019
      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
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Oct 29, 2019

        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).

    • MrRAMMount's avatar
      MrRAMMount
      Copper Contributor
      Oct 29, 2019

      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

Resources