Vlookup post codes/zipcodes query

Can anyone help me on this one? I am using vlookup to search for a post
code (zipcode) in an array. I have sorted the post code column in the array
into ascending order. However I am getting '#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is confused.
for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to an
individual numeric value, in case vlook up finds this easier to search.
However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to approach this.
The post codes are the only identifiers that I can search against.

Any advice gratefully received.

Pete G
Derby
England

--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a post
code (zipcode) in an array. I have sorted the post code column in the
array
into ascending order. However I am getting '#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is confused.
for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to an
individual numeric value, in case vlook up finds this easier to search.
However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to approach
this.
The post codes are the only identifiers that I can search against.

Any advice gratefully received.

Pete G
Derby
England

--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a post
code (zipcode) in an array. I have sorted the post code column in the
array
into ascending order. However I am getting '#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is confused.
for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to an
individual numeric value, in case vlook up finds this easier to search.
However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to approach
this.
The post codes are the only identifiers that I can search against.

Any advice gratefully received.

Pete G
Derby
England

Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a post
code (zipcode) in an array. I have sorted the post code column in the
array
into ascending order. However I am getting '#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is confused.
for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to an
individual numeric value, in case vlook up finds this easier to search.
However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to approach
this.
The post codes are the only identifiers that I can search against.

Any advice gratefully received.

Pete G
Derby
England

Thanks Bob, however I have tried the formula you suggest but still get a
#n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a post
code (zipcode) in an array. I have sorted the post code column in the
array
into ascending order. However I am getting '#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused.
for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to an
individual numeric value, in case vlook up finds this easier to search.
However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to approach
this.
The post codes are the only identifiers that I can search against.

Any advice gratefully received.

Pete G
Derby
England

Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.

--
Regards
Frank Kabel
Frankfurt, Germany


pgiblin wrote:
Thanks Bob, however I have tried the formula you suggest but still
get a #n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a
post code (zipcode) in an array. I have sorted the post code
column in the array into ascending order. However I am getting
'#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused. for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to
an individual numeric value, in case vlook up finds this easier to
search. However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to
approach this. The post codes are the only identifiers that I can
search against.

Any advice gratefully received.

Pete G
Derby
England

Pete,

Try this as well

=VLOOKUP(TRIM(A17),I1:I8,2,FALSE)

adjust the cell references of course

--

HTH

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

"Frank Kabel" wrote in message
...
Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.

--
Regards
Frank Kabel
Frankfurt, Germany


pgiblin wrote:
Thanks Bob, however I have tried the formula you suggest but still
get a #n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a
post code (zipcode) in an array. I have sorted the post code
column in the array into ascending order. However I am getting
'#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused. for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to
an individual numeric value, in case vlook up finds this easier to
search. However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to
approach this. The post codes are the only identifiers that I can
search against.

Any advice gratefully received.

Pete G
Derby
England

Sorry Guys, still no go

The formula reads =VLOOKUP(B4,CH4:CK257,1)

The data in cell B4 reads CB23PA, the data in Cell CK20 reads exactly the
same (no spaces or anything) CB23PA. So it should find that cell and return
the data in column1 which is a serial number?

What am I missing?






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

Try this as well

=VLOOKUP(TRIM(A17),I1:I8,2,FALSE)

adjust the cell references of course

--

HTH

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

"Frank Kabel" wrote in message
...
Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.

--
Regards
Frank Kabel
Frankfurt, Germany


pgiblin wrote:
Thanks Bob, however I have tried the formula you suggest but still
get a #n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a
post code (zipcode) in an array. I have sorted the post code
column in the array into ascending order. However I am getting
'#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused. for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to
an individual numeric value, in case vlook up finds this easier to
search. However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to
approach this. The post codes are the only identifiers that I can
search against.

Any advice gratefully received.

Pete G
Derby
England

Seems odd.

Want to post me your workbook and I can take a look.

--

HTH

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

"pgiblin" wrote in message
...

Sorry Guys, still no go

The formula reads =VLOOKUP(B4,CH4:CK257,1)

The data in cell B4 reads CB23PA, the data in Cell CK20 reads exactly the
same (no spaces or anything) CB23PA. So it should find that cell and
return
the data in column1 which is a serial number?

What am I missing?






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

Try this as well

=VLOOKUP(TRIM(A17),I1:I8,2,FALSE)

adjust the cell references of course

--

HTH

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

"Frank Kabel" wrote in message
...
Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.

--
Regards
Frank Kabel
Frankfurt, Germany


pgiblin wrote:
Thanks Bob, however I have tried the formula you suggest but still
get a #n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the
data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for
a
post code (zipcode) in an array. I have sorted the post code
column in the array into ascending order. However I am getting
'#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused. for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to
an individual numeric value, in case vlook up finds this easier to
search. However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to
approach this. The post codes are the only identifiers that I can
search against.

Any advice gratefully received.

Pete G
Derby
England

Are the codes in your table at CH4:CK257 sorted on column CH? If not, you need
to add the 4th argument, 0, to your VLOOKUP formula. That may be a good idea
anyway -- without it, if the list is sorted, and CB23OZ is present but not
CB23PA, the function will return CB23OZ.

On Mon, 26 Jul 2004 21:09:27 +0100, "pgiblin"
wrote:


Sorry Guys, still no go

The formula reads =VLOOKUP(B4,CH4:CK257,1)

The data in cell B4 reads CB23PA, the data in Cell CK20 reads exactly the
same (no spaces or anything) CB23PA. So it should find that cell and return
the data in column1 which is a serial number?

What am I missing?






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

Try this as well

=VLOOKUP(TRIM(A17),I1:I8,2,FALSE)

adjust the cell references of course

--

HTH

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

"Frank Kabel" wrote in message
...
Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.

--
Regards
Frank Kabel
Frankfurt, Germany


pgiblin wrote:
Thanks Bob, however I have tried the formula you suggest but still
get a #n/a result.

"Bob Phillips" wrote in message
...
Use

=VLOOKUP(lookup_value,lookup_table,2,FALSE)

or whatever column offset you need, the False will not need the data
sorted.


--

HTH

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

"pgiblin" wrote in message
...
Can anyone help me on this one? I am using vlookup to search for a
post code (zipcode) in an array. I have sorted the post code
column in the array into ascending order. However I am getting
'#N/A' as a result.

I have removed all spaces from the post codes.

I think it is because although they are 'sorted' the vlookup is
confused. for example a section of the list may look like this,

AL21RE
B244JH
B244UB
B322AU
B333SD
BA11BP
BS16BN
CB11ER

I have considered trying to convert each individual 'post code' to
an individual numeric value, in case vlook up finds this easier to
search. However I have been unable to find a formula that suits.

Is there anything that I am doing wrong, or any other way to
approach this. The post codes are the only identifiers that I can
search against.

Any advice gratefully received.

Pete G
Derby
England