Concatenate, Ignoring Blanks and VLOOKUP

[squeezin]

Ok, I'm trying to do the following :

I have a row of data in one sheet with a unique code in Column A.

In sheet 2 I also have the same unique code in Column A, in columns B-G I have some names of people but some of these could be blank

I basically want to lookup the unique codes in Column A and then for a match concatenate B-G in a single cell (Column B) on sheet 1.

Also, rather than the data put into the cell being delimited by a comma, I need to have each name on a seperate line within the cell.

 

[Sal Paradise]

=INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A),)&CHAR(10)&INDEX(Sheet2!C:C,MATCH(A1,Sheet2!A:A),)&CHAR(10)&INDEX(Sheet2!D:D,MATCH(A1,Sheet2!A:A),)&CHAR(10)&INDEX(Sheet2!E:E,MATCH(A1,Sheet2!A:A),)&CHAR(10)&INDEX(Sheet2!F:F,MATCH(A1,Sheet2!A:A),)&CHAR(10)&INDEX(Sheet2!G:G,MATCH(A1,Sheet2!A:A),)

 

[squeezin]

Thanks Sal, will give that a go, does it ignore blanks cells?

I had a go myself and came up with this :

=(VLOOKUP($A23,Sheet1!B:N,8,0))&CHAR(10)&(VLOOKUP($A23,Sheet1!B:N,9,0))&CHAR(10)&(VLOOKUP($A23,Sheet1!B:N,10,0))&CHAR(10)&(VLOOKUP($A23,Sheet1!B:N,11,0))&CHAR(10)&(VLOOKUP($A23,Sheet1!B:N,12,0)&CHAR(10)&(VLOOKUP($A23,Sheet1!B:N,13,0)))

But it doesn't ignore blanks cells, can't figure out how to do that.

 

[Sal Paradise]

Blanks will just appear as blank lines. What do you want to happen?