Index Match Postcodes

[steveh1873]

Hi All,

I have a scenario where I have made a County/Country generator based upon an input cell's text (which in this case is partial Postcodes e.g. LU1, MK11...etc) using the formula

=INDEX($A$2:$A$2873,MATCH(E2,$B$2:$B$2873,))

where

$A$2:$A$2873 = List of Counties
E2 = My input text cell
$B$2:$B$2873 = List of Partial Postcodes (LU1, MK1....)


The issue I have is, is that this only works when the exact match occurs (LU1, MK11...etc) but what I need is for the formula to pick up the partials (LU1, MK11...etc) when a full Postcode is entered (e.g. LU18NP). Another little caveat is that the postcode list in the table has either 3 or 4 characters.

Is someone able to assist me in tweaking the formula to complete the job that is needed.

 

[anglais428]

How many different Postcode string lengths can there be? E.g. LU1 1XX or can it be LU10 20XX? Also will there be a space separator - i.e LU1 8HS or will it be LU18HS?

 

[ParamRay]

.
.

For UK postcodes, the postcode area is always the full postcode unit minus the last 3 characters. So, if you use a formula like =TRIM(LEFT(A1,LEN(A1)-3)), where cell A1 contains your full postcode unit, then it will return the postcode area. (By using TRIM, it doesn't matter whether or not the full postcode unit contains a space.)

 

[steveh1873]

anglais428: Thanks for the reply,

String Lengths vary between the following: L1 1XX, L11 1XX, LU1 1XX & LU11 1XX.

They also currently have spaces. Can this be completed with the spaces still present (the postcodes are currently presented with a space at the point of delivery, however this doesn't need to be absolute if it makes this process easier)

 

[steve the fish]

gpeacock had you on the right track.

Try:

=IFERROR(IFERROR(INDEX($A$1:$A$5000,MATCH(TRIM(LEFT(E2,LEN(E2)-3)),$B$1:$B$5000,0)),INDEX($A$1:$A$5000,MATCH(E2,$B$1:$B$5000,0))),"Code Not Found")

 

[steveh1873]

Thank you ever so much.

Absolute gem of a formula. I've just inserted this between Generator and County which creates an extra step to getting the end result. My original formula still remains in tact, but this is a cheeky & necessary addition, so thank you once again

 

[steve the fish]

That formula should return County. For Country change the A1:A5000 part to C1:C5000.