Hi.

I've got a list of M25 postcode sectors on a sheet called Postcode List
- 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 Left
lookup value going up by one e.g left 2, left 3 etc. Then a if
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 Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567890

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

VBA Noob wrote:
Hi.

I've got a list of M25 postcode sectors on a sheet called Postcode List
- 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 Left
lookup value going up by one e.g left 2, left 3 etc. Then a if
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 Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567890

Thanks Pete.

Will give it a go

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567890

Post back if you need any further help.

Pete

VBA Noob wrote:
Thanks Pete.

Will give it a go

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567890

Perhaps

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567890

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567890

Ah yes, of course. Just to be consistent I missed it out of both
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))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=567890