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 cri...
TEXTJOIN
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
        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'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