IF Formula for pulling text from multiple cells together

[Judy Tran]

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

[#2]

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

[#3]

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.

[#4]

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