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

Thank you,

Judy

3 Replies

# Re: IF Formula for pulling text from multiple cells together

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

# Re: IF Formula for pulling text from multiple cells together

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.

# Re: IF Formula for pulling text from multiple cells together

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