Robert,

Kludgy but

=IF(vlookup(sitecode,addressrange, 4)<"",vlookup(sitecode,addressrange,
4),IF(vlookup(sitecode,addressrange, 5)<"",vlookup(sitecode,addressrange,
5),vlookup(sitecode,addressrange, 6)))

then next row

=IF(vlookup(sitecode,addressrange, 5)<"",vlookup(sitecode,addressrange,
5),vlookup(sitecode,addressrange, 6))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert" wrote in message
...
Hi,

Do I need a standard formula or an array to solve the following problem?

I have several vlookup formula each in a separate row that look up a site
code and returns parts of an address eg.

=vlookup(sitecode,addressrange, 2) returns add1
=vlookup(sitecode,addressrange, 3) returns add2
=vlookup(sitecode,addressrange, 4) returns add3
=vlookup(sitecode,addressrange, 5) returns add4
=vlookup(sitecode,addressrange, 6) returns zip

This works fine when the address has 5 elements but where there's only 2
elements of an address plus zip code the retuned address looks like the
following

add1
add2
[blank row]
[blank row]
zip code

What formula can I use to get the address to display correctly i.e.

add1
add2
zip code
[blank row]
[blank row]

Hopefully I have explained myself!