Forum Discussion

Judy Tran's avatar
Judy Tran
Copper Contributor
Dec 15, 2020

IF Formula for pulling text from multiple cells together

Hi guys,

Hoping to pick someone's brain on my below conundrum.

 

I am after a formula that will pull together text in multiple cells based on criteria listed in another cell.

 

E.g. On one sheet, I have text corresponding to numbers 1-50 down a column.

 

On another sheet, there are cells referencing multiple numbers such as 1,4,5,6,7,10.

 

I would like to do a formula that will pull together the text corresponding to those numbers into one cell.

 

Is this possible? I am thinking to use an IF formula but not sure how to start. I have attached a sample workbook of what I am trying to achieve.

 

Any answers would be appreciated!


Thank you,

Judy

excel
Excel on Mac
Formulas and Functions

3 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 15, 2020

    Judy Tran 

    Try this.

     

    =TEXTJOIN(" ",TRUE,XLOOKUP(FILTERXML("<y><z>"&SUBSTITUTE(A2,", ","</z><z>")&"</z></y>","//z"),Sheet1!$A$1:$A$30,Sheet1!$B$1:$B$30))

     

    EDIT: Replaced german function name with english function name (XLOOKUP).

     

    • Judy Tran's avatar
      Judy Tran
      Copper Contributor
      Dec 16, 2020
      Hi Detlef, thank you! Unfortunately, I am on a Mac and it seems the FILTERXML function isn't available on it. Is there another way to do this? Thanks so much for helping out on this, much appreciated.
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Dec 17, 2020

        Judy Tran 

        Helper cells in columns C:F. In C2:

        =XLOOKUP(--TRIM(MID(SUBSTITUTE($A2,", ",REPT(" ",100)),COLUMN(A2)*100-99,99)),Sheet1!$A$1:$A$30,Sheet1!$B$1:$B$30)

        And copy to the right,

        In B2:

        =TEXTJOIN(" ",TRUE,C2:F2)

Resources