Array or Vlookup

[Robert]

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 Phillips]

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]

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

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)

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 Phillips]

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)

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

Robert,

Kludgy but

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

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]

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

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)

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

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 Phillips]

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)

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

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)

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


Robert,

Kludgy but

=IF(vlookup(sitecode,addressrange,

4),IF(vlookup(sitecode,addressrange,

5),vlookup(sitecode,addressrange, 6)))

then next row

=IF(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)

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!