Bob,

Have done as suggested but the issue is that the last row repeats the zip
code as there is always an entry in this offset position and the previous
formula also checks and enters where it hasn't another address entry.

I'm sure and array formula would do what I need, I just cant figure it out.

Thanks, Robert


"Bob Phillips" wrote in message
...
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!