# Vlookup based on contents of a cell

#### geordie_ben

##### Board Regular
I've gotten a list of post codes and their counties.

This is how the data is presented:

Postcode areaPostcode districtsPost townFormer postal county
ABAB10, AB11, AB12, AB15, AB16,
AB21, AB22, AB23, AB24, AB25,
AB99non-geo
Aberdeen<small style="font-size: 11.9px;">(Aberdeenshire)</small>

</tbody>
I need to do some type of lookup (I think), where the first part of a full post code being AB22 would show Aberdeenshire

Is there a type of formula I could use to do this?

#### FormR

##### MrExcel MVP
Hi, you could extract the postcode area from a full postcode with a formula like this:

You could then do a standard VLOOKUP() to the get post town or county.

#### geordie_ben

##### Board Regular
Hi

Thanks for the super quick reply

The above was a snippet of every post code in the UK

Would you formula still work?

I can't do the lookup on "AB" in the above example as some Postcode Areas have more than County inside them

#### FormR

##### MrExcel MVP
The above was a snippet of every post code in the UK
Hi, a slightly bigger snippet, along with a few examples of the inputs and expected results be helpful. But pending that - here's another stab.

#### geordie_ben

##### Board Regular
Hi

The list of information is from here - https://en.wikipedia.org/wiki/List_of_postcode_districts_in_the_United_Kingdom

This is a decent snippet:

 B B1, B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21, B23, B24, B25, B26, B27, B28, B29, B30, B31, B32, B33, B34, B35, B36, B37, B38, B40, B42, B43, B44, B45, B46, B47, B48, B99non-geo Birmingham (West Midlands) B B49, B50 Alcester Warwickshire B B60, B61 Bromsgrove Worcestershire B B62, B63 Halesowen West Midlands B B64 Cradley Heath West Midlands B B65 Rowley Regis West Midlands B B66, B67 Smethwick West Midlands B B68, B69 OLDBURY West Midlands B B70, B71 West Bromwich West Midlands B B72, B73, B74, B75, B76 Sutton Coldfield West Midlands B B77, B78, B79 TAMWORTH Staffordshire B B80 STUDLEY Warwickshire B B90, B91, B92, B93, B94 Solihull West Midlands

<tbody>
</tbody>
I have a list of customer post codes, and I need to establish which County they're in, which is Column D in the above example

Example:

Postcode is B75 1AZ

I've done a text to columns, delimited on space to get the first part of the Post Code (I've manually checked them and they're all good - phew!)

The formula would then bring back the County as West Midlands

Hopefully this helps

#### FormR

##### MrExcel MVP
I've done a text to columns, delimited on space to get the first part of the Post Code
Hi, you can try this:

#### geordie_ben

##### Board Regular
That's worked perfectly, thanks!!

Out of interest, why did you change the formula?

#### FormR

##### MrExcel MVP
Out of interest, why did you change the formula?
Hi, I realised that the VLOOKUP() with the wildcards is not robust. It would match B2 with B22 if B22 happened to occur in a row before B2 for example.

#### geordie_ben

##### Board Regular
Ah, gotcha

I think luckily they're all in alphabetical order.