Vlookup post codes/zipcodes query

  • Thread starter Thread starter pgiblin
  • Start date Start date
P

pgiblin

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)
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
Hi
this would indicate there's no exact match. Have you checked there's a
valid match?. Also please post your used formula.
 
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)
 
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?
 
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)
 
Hi Pete,

For Vlookup to work, you need to put the column which has the reference
value before the column which has the serial number. You can drag and
drop (keeping ctrl pressed) column CK before column CH. Now column CK
becomes column CH and the Old column CH becomes column CI.

Then use the formula.
=VLOOKUP(B4,$CH$4:$CI$257,1,False)

The column with the lookup value should be the first column in the
lookup table.

CH CI
------ ---
AL21RE 20
B244JH 30
B244UB 40
B322AU 50
B333SD 60
BA11BP 70
BS16BN 80
CB11ER 90

Hope this helps.

Regards,
Prashanth
 
Back
Top