Joining VLookups with line breaks but ignoring blanks

Giganews Newsgroups
Subject: Joining VLookups with line breaks but ignoring blanks
Posted by:  Andy Roberts (an…@blue-bean.co.uk)
Date: Fri, 14 Sep 2012

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

Replies