I have a spreadsheet of addresses e.g.

Name Address1 Address2 Address3 Postcode

In a different workbook I performing a lookup on the Name cell and in a
merged celled below I'm bringing in the address information as a joined up
lookup with character returns so I get

Name
Address1
Address2
Address3
Postcode

I am using the following equation...

=TRIM(VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,2,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,3,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,4,0)&CHAR(10)&VLOOKUP(C9,'[B.xlsx]Sheet1'!$A$8:$Q$50,5,0))

The problem occurs when I have an address which doesn't have any data in the
Address3 column so my result looks like...

Name
Address1
Address2

Postcode

How can I amend my formula to ignore the blank cell so that I always get
each cell content under each other with no blank row.

e.g.
Name
Address1
Address2
Address3
Postcode

or

Name
Address1
Address2
Postcode

Regards

Andy
Office 2010
Win XP Pro SP3