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