IF Formula for pulling text from multiple cells together

%3CLINGO-SUB%20id%3D%22lingo-sub-1988011%22%20slang%3D%22en-US%22%3EIF%20Formula%20for%20pulling%20text%20from%20multiple%20cells%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1988011%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3EHoping%20to%20pick%20someone's%20brain%20on%20my%20below%20conundrum.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20after%20a%20formula%20that%20will%20pull%20together%20text%20in%20multiple%20cells%20based%20on%20criteria%20listed%20in%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g.%20On%20one%20sheet%2C%20I%20have%20text%20corresponding%20to%20numbers%201-50%20down%20a%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20another%20sheet%2C%20there%20are%20cells%20referencing%20multiple%20numbers%20such%20as%201%2C4%2C5%2C6%2C7%2C10.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20do%20a%20formula%20that%20will%20pull%20together%20the%20text%20corresponding%20to%20those%20numbers%20into%20one%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20I%20am%20thinking%20to%20use%20an%20IF%20formula%20but%20not%20sure%20how%20to%20start.%20I%20have%20attached%20a%20sample%20workbook%20of%20what%20I%20am%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20answers%20would%20be%20appreciated!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%2C%3C%2FP%3E%3CP%3EJudy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1988011%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1988073%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20for%20pulling%20text%20from%20multiple%20cells%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1988073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F114995%22%20target%3D%22_blank%22%3E%40Judy%20Tran%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXTJOIN(%22%20%22%2CTRUE%2CXVERWEIS(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(A2%2C%22%2C%20%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2CSheet1!%24A%241%3A%24A%2430%2CSheet1!%24B%241%3A%24B%2430))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1995759%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Formula%20for%20pulling%20text%20from%20multiple%20cells%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1995759%22%20slang%3D%22en-US%22%3EHi%20Detlef%2C%20thank%20you!%20Unfortunately%2C%20I%20am%20on%20a%20Mac%20and%20it%20seems%20the%20FILTERXML%20function%20isn't%20available%20on%20it.%20Is%20there%20another%20way%20to%20do%20this%3F%20Thanks%20so%20much%20for%20helping%20out%20on%20this%2C%20much%20appreciated.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

3 Replies

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

 

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.

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