# Index Match Postcodes

#### steveh1873

##### New Member
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.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### anglais428

##### Well-known Member
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

##### Well-known Member
.
.

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

##### New Member

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

##### Well-known Member
gpeacock had you on the right track.

Try:

#### steveh1873

##### New Member
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

##### Well-known Member
That formula should return County. For Country change the A1:A5000 part to C1:C5000.

Replies
3
Views
222
Replies
7
Views
535
Replies
10
Views
163
Replies
1
Views
84
Replies
6
Views
335

1,195,588
Messages
6,010,607
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back