Vlookup post codes/zipcodes query

[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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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]

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

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[pgiblin]

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]

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)

 

[Myrna Larson]

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.

 

[Prashanth]

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