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!

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!

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!

Robert,

I didn't include a zip code formula, but this should do it.

=IF(OR(vlookup(sitecode,addressrange, 4)="",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
...
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!

Bob,

Sorry but I'm not getting this to work, entering the formula simply puts the
zip code in twice, whereas I'm looking for it to only appear once.

I shall keep trying!

Thanks, Robert

"Bob Phillips" wrote in message
...
Robert,

I didn't include a zip code formula, but this should do it.

=IF(OR(vlookup(sitecode,addressrange, 4)="",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
...
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!

Robert,

Do you want to email the workbook (bob . phillips @ tiscali . co . uk -
remove the spaces) and I will take a look?

--

HTH

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

"Robert" wrote in message
...
Bob,

Sorry but I'm not getting this to work, entering the formula simply puts
the
zip code in twice, whereas I'm looking for it to only appear once.

I shall keep trying!

Thanks, Robert

"Bob Phillips" wrote in message
...
Robert,

I didn't include a zip code formula, but this should do it.

=IF(OR(vlookup(sitecode,addressrange,
4)="",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
...
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!