Match a UK postcode from a table of postcodes
 

Hi - using Excel 2010 but could use Office 365 if that helps!


A customer has a postcode and we need to identify the Zone in order to calculate cost of delivery. It could be any of the following formats;

BA12 1AA
BA1 2SD
B4 2GH
SW1W 8BB


I have a sheet with columns which represent Zones and each Zone has different UK postcode regions and sectors so for example Zone C includes

B
IP
LD
ME
NE
NR
PE21
PE22
PE23
PE24
PE25
PE30

You can see that the first 1 or 2 letters of the postcode are important but where a postcode region is larger the numbers become important to ensure the correct Zone is chosen. Sometimes the number after the space is also used.

I have 5 formulae for each Zone to check if the postcode - or part of it - is a match within a Zone and these are replicated for each Zone so I end up with lots of 0's and only one answer with 1. I am checking for the following matches;

KA6 5NE
KA6 5
KA6
KA
K

When I find the correct answer it will be in a column and I need that column heading to obtain the Zone letter. If anyone can compile that into a single cell formula then I would be full of thanks.

Thanks for reading

Corporal Smudge