M25 postcode

V

VBA Noob

Hi.

I've got a list of M25 postcode sectors on a sheet called Postcode Lis
- M25e.g

EC1
EC2
EC3
EC4
WC1
WC2
N1
N2
N3
N4
N5

I have this formula in col I with formulas going up to M with Lef
lookup value going up by one e.g left 2, left 3 etc. Then a i
statement to say true or false for inside M25.

attached are some sample postcodes

CF71 7UD
GU14 0LG
SK4 3EA
KT2 5LZ
N5 1LH - M25
SM4 4ST
BD8 0LZ

=IF(ISERROR(VLOOKUP(LEFT($A2,2),'Postcode List
M25'!$A$1:$A$258,1,0)),"No",(VLOOKUP(LEFT($A2,2),'Postcode List
M25'!$A$1:$A$258,1,0)))

Is there an easier way to do this

Any help or ideas would be good

VBA Noo
 
P

Pete_UK

I do something similar to this with telephone numbers, where the STD
code can vary in length (particularly considering international calls).
I have managed to get it into one formula checking from 2 to 10
characters of the phone number and taking the match with the largest
number of characters.

Yours is a slightly different problem, however. Consider your first
example, CF71 7UD - you really need a match on CF71 because if you were
to get a match on CF7 but not on CF71, then you could not take this as
being in the M25 zone.

So, instead of taking left 2, left 3, left 4 etc in different columns,
I think you should look for the space in the postcode (using SEARCH)
and then using LEFT take all the characters up to that and use that for
your VLOOKUP - you would only need one formula.

Hope this helps.

Pete
 
D

daddylonglegs

Perhaps

=IF(ISNA(MATCH(LEFT(FIND(" ",$A2)-1),'Postcode List -
M25'!$A$1:$A$258,0)),"No",LEFT(FIND(" ",$A2)-1))
 
V

VBA Noob

Thanks daddylonglegs

Think you forgot the bit in bold but got it to work

=IF(ISNA(MATCH(LEFT(*$A2*,FIND(" ",$A2)-1),'Postcode List -
M25'!$A$1:$A$258,0)),"No",LEFT(FIND(" ",$A2)-1))

VBA Noob
 
D

daddylonglegs

Ah yes, of course. Just to be consistent I missed it out of bot
parts..:)

should be

=IF(ISNA(MATCH(LEFT(*$A2*,FIND(" ",$A2)-1),'Postcode List
M25'!$A$1:$A$258,0)),"No",LEFT(*$A2*,FIND(" ",$A2)-1)
 
Top