need help with vlookup on uk postcodes

[oli merge]

Hello all,

Am a bit stuck here, probably partly because its a friday afternoon...

Basically i am making a price calculator which works out how much it will
cost to deliver to a postcode input by the user. There are 3 different price
zones, and I have a list of which price zones go with which postal areas and
districts.

(NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is
the postal area, "cv21" is the postal district)


The problem is that not all the postal areas are completely in one zone, so
we have to look at the postal district - ie. instead of just looking for "PE"
postal area it needs to look for PE1 or PE17 etc. Thats where the problem
comes in.

I have already spent ages working out how to make it find only the leading
letters at the start by testing each letter consecutively to see if its a
number with the code:

=IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISNUMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(VALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4))))


That works ok, another cell now looks at whether its an N/A error or not,
and if it is tries including the some numbers as well:

=IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones
data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcode,4,1))),VLOOKUP(LEFT(postcode,4),'Zones
data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones
data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0)))

Thing is, its not working on the postcodes which should be using the postal
district because that is a split area. e.g. the postcode PE17 xyz should be
identified as a zone two according to the table of zones data:

Postcode start Zone
....
CV 3
PE14 1
PE15 1
PE16 1
PE17 2
....etc...

But anything that requires numbers as part of the search is turning up a N/A
error and I am too tired to follow my own formula now and work out where i am
going wrong.

Please help with possibly the longest question of all time!

Thanks,
Oli

 

[Roger Govier]

Hi Oli

to get the Postal District just use
=LEFT(A1,FIND(" ",A1)-1)

To get the Zone use
=MID(A1,FIND(" ",A1)+1,1)

 

[Toppers]

Sorry , despite the long posting, it's not clear to me what you are
doing/trying to?

Are you trying to determine the postal district (and/or district) and then
lookup the zone? why is this a problem?

(I am UK-based so understand the post code format which includes several
variants on the postal district).

Perhaps a few examples would help (me certainly!).

 

[oli merge]

Hi,

I would but i cant guareentee that the space will be in there, maybe i will
just validate it to make the user have to put the space in.

Out of interest anyone know what wud need to do if i wanted to do this
without the space?

 

[Roger Govier]

Hi

It makes life much easier, if you force the original input to be
correct.

If so, then assuming your data entry will be in column A first set up a
number of Named formulae with
Insert>Name>Define
Name First
Refers to =LEFT($A1,FIND(" ",$A1)-1)
Name Last
Refers to =RIGHT($A1,3)
Name start
Refers to =OR(ISTEXT(LEFT(first)),ISTEXT(LEFT(first,2)))
Name mid
Refers to =OR(ISTEXT(MID(first,3,1)),ISTEXT(MID(first,4,1)))
Name end
Refers to =AND(ISNUMBER(--(LEFT(last))),ISTEXT(RIGHT(last,2)))
Name numbers
Refers to
=AND(OR(ISNUMBER(--(MID(first,2,1))),ISNUMBER(--(MID(first,2,2))),
ISNUMBER(--(MID(first,3,1))),ISNUMBER(--(MID(first,3,2)))),
NOT(ISNUMBER(--(RIGHT(first,3)))))

Mark the range of cells in column A where you want the users to enter
Postcode,
Data>Validation>Custom> =AND(start,mid,end,numbers)
Remove tick mark from ignore Blank
Got to Error Alert tab and ensure there is a tick mark in Show error
Alert. Type a message here if you wish.

If not for column A, then change all references from A to the relevant
column letter.

 

[oli merge]

Hi Toppers,

The problem I am getting is when I put in a postcodes that doesnt fit
entirely within one zone. e.g. PE is split into two zones, lets say PE1-20 is
zone 1 and PE21-end is zone 2. Without using the gap to decide how many
numbers are to be included with the PE , its tricky to get the vlookup to
look at the right amount of numbers from the rest of the postcode. e.g. PE21
is in danger of being mistaken for PE2 the way i was doing it. Also, you can
have PE2 1JF which is in danger of being mistaken for PE21 if I dont use the
space to select which numbers are part of the postal district.

I am a bit more awake today so will have a look at Roger's solution.

I also think i might be able to find another way around it by adding a
couple of "IF(ISNA(..." formulas around what i am already using. not sure
about that tho, will have to test it.

Will let you know what solution I decide to use, thanks for your help
everyone.

 

[oli merge]

strangely it seems i have already done this and it has started working again.

Im sure im using the same test data as last week, but now im getting the
correct answers for the split postcodes.

perhaps it only errors on certain postcodes, I will have to check,

Will let you all know.

 

[oli merge]

right, I cant seem to break it again. so now im after someone to spot where
the following technique might go wrong:

I have a single cell for postcode input (named postcode, but also referred
to as D5 just because Im not the tidiest of excel users).

From that I work out the postal district in cell D13:

=IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISNUMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(VALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4))))

From that, I work out what zone (using a VLookup against another sheet where
each postal area or postal district where applicable is on a line next to
what zone) in cell D12 using this formula:

=IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,2,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones
data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcode,4,1))),VLOOKUP(LEFT(postcode,4),'Zones
data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones
data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones
data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones
data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0)))

Now, i think this is the cell where I trap for postcodes that are split
between different zones with the ISNA. I still cant quite follow that
convuluted formula though, so embarrisingly am not sure why it works and what
I have to do to make it go wrong. im sure it wasnt working correctly on
friday, but now it does seem to be.

i need some genius to help here if possible.

Once i have the zone, obviously its very easy to look that up on a pricing
table so i wont go into that. Thanks then, if noone can see where an error
might crop up I will close this thread since the problem is not reproduceable