Formula to return mulitple columns of data into one cell

**Lizzie123** · 05-12-2013, 08:41 PM

Hi,

I have a spreadsheet where I have a list of fund codes going down column A and each fund has 'sibling funds'. These sibling fund are listed individually across the page i.e one in each column across from B to U.

I have 2 SHEETS. SHEET 1 is kind of like a form and SHEET 2 is the refence tab that has all of the funds listed with the sibling funds listed next to it in subsequent columns.

The fund code has already been looked up in SHEET 1 and is showing in cell E11 then I need to reference this fund code in SHEET 1 CELL E11 and then use that fund code to look it up in SHEET 2 and return all of the sibling codes into cell E14 in SHEET 1.

Can anyone help with this? I think it might be a VLOOKUP and an INDEX or MATCH but i cant figure it out.

Any help appreciated!

Thanks,
Lizzie

**p24leclerc** · 05-12-2013, 09:54 PM

Do you mean you want all siblings to be concatenated into one cell "E14" probably seperated by a comma?
Or do you want a kind of list box giving you the different siblings related to the specific fund stated in cell "E11" to choose from?

**Lizzie123** · 05-12-2013, 09:56 PM

Yes I want all siblings to be concatenated into one cell "E14" - each one seperated by a comma..

**p24leclerc** · 05-13-2013, 04:38 PM

I tried some formulas and finally did some research on the NET about your question. I think it is not doable with a formula. A macro will do it though. I was able to find some with Google. If you don't find what you need, let me know, I'll help you.
Regards

**Lizzie123** · 05-13-2013, 06:54 PM

Thanks for your help. I actually have an answer if you're interested:

=IFERROR(IF(VLOOKUP(E11,'SHEET2!A:W,4,)>0,VLOOKUP(E11,'SHEET2'!A:W,4,)&"; ","-")&IF(VLOOKUP(E11,'SHEET2'!A:W,5,)>0,VLOOKUP(E11,'SHEET2'!A:W,5,)&"; ","")&IF(VLOOKUP(E11,'SHEET2'!A:W,6,)>0,VLOOKUP(E11,'SHEET2'!A:W,6,)&"; ","")&

Just repeated for each column!!