Vlookup based on contents of a cell

geordie_ben

Board Regular
Joined
Jul 27, 2009
Messages
122
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>

<thead>
</thead><tbody>
</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
Joined
Aug 18, 2011
Messages
6,152
Office Version
365
Platform
Windows
Hi, you could extract the postcode area from a full postcode with a formula like this:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Full postcode</td><td style=";">Postcode area</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">AB22 0AA</td><td style="background-color: #FFFF00;;">AB</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,AGGREGATE(<font color="Red">15,6,FIND(<font color="Green">{0,1,2,3,4,5,6,7,8,9},A2</font>),1</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

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

geordie_ben

Board Regular
Joined
Jul 27, 2009
Messages
122
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
Joined
Aug 18, 2011
Messages
6,152
Office Version
365
Platform
Windows
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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Postcode area</td><td style=";">Postcode districts</td><td style=";">Post town</td><td style=";">Former postal county</td><td style="text-align: right;;"></td><td style=";">Full postcode</td><td style=";">town</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">AB</td><td style=";">AB10, AB11, AB12, AB15, AB16, AB21, AB22, AB23, AB24, AB25, AB99non-geo</td><td style=";">Aberdeen</td><td style=";">(Aberdeenshire)</td><td style="text-align: right;;"></td><td style=";">AB22 0AA</td><td style="background-color: #FFFF00;;">Aberdeen</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">"*"&LEFT(<font color="Red">F2,FIND(<font color="Green">" ",F2&" "</font>)-1</font>)&"*",B:C,2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

geordie_ben

Board Regular
Joined
Jul 27, 2009
Messages
122
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:

BB1, 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<small style="font-size: 11.9px;">(West Midlands)</small>
BB49,
B50
AlcesterWarwickshire
BB60, B61BromsgroveWorcestershire
BB62, B63HalesowenWest Midlands
BB64Cradley HeathWest Midlands
BB65Rowley RegisWest Midlands
BB66, B67SmethwickWest Midlands
BB68, B69OLDBURYWest Midlands
BB70, B71West BromwichWest Midlands
BB72, B73, B74, B75, B76Sutton ColdfieldWest Midlands
BB77, B78, B79TAMWORTHStaffordshire
BB80STUDLEYWarwickshire
BB90, B91, B92, B93, B94SolihullWest 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
Joined
Aug 18, 2011
Messages
6,152
Office Version
365
Platform
Windows
I've done a text to columns, delimited on space to get the first part of the Post Code
Hi, you can try this:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">B</td><td style=";">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</td><td style=";">Birmingham</td><td style=";">(West Midlands)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">B</td><td style=";">B49, B50</td><td style=";">Alcester</td><td style=";">Warwickshire</td><td style="text-align: right;;"></td><td style=";">B75</td><td style="background-color: #FFFF00;;">West Midlands</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B</td><td style=";">B60, B61</td><td style=";">Bromsgrove</td><td style=";">Worcestershire</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">B</td><td style=";">B62, B63</td><td style=";">Halesowen</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B</td><td style=";">B64</td><td style=";">Cradley Heath</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">B</td><td style=";">B65</td><td style=";">Rowley Regis</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B</td><td style=";">B66, B67</td><td style=";">Smethwick</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B</td><td style=";">B68, B69</td><td style=";">OLDBURY</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B</td><td style=";">B70, B71</td><td style=";">West Bromwich</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B</td><td style=";">B72, B73, B74, B75, B76</td><td style=";">Sutton Coldfield</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">B</td><td style=";">B77, B78, B79</td><td style=";">TAMWORTH</td><td style=";">Staffordshire</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">B</td><td style=";">B80</td><td style=";">STUDLEY</td><td style=";">Warwickshire</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">B</td><td style=";">B90, B91, B92, B93, B94</td><td style=";">Solihull</td><td style=";">West Midlands</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=LOOKUP(<font color="Blue">2,1/(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">F2&",",$B$1:$B$999&","</font>)</font>)</font>),$D$1:$D$999</font>)</td></tr></tbody></table></td></tr></table><br />
 

geordie_ben

Board Regular
Joined
Jul 27, 2009
Messages
122
That's worked perfectly, thanks!!

Out of interest, why did you change the formula?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,152
Office Version
365
Platform
Windows
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
Joined
Jul 27, 2009
Messages
122
Ah, gotcha

I think luckily they're all in alphabetical order.

Thanks for your help
 

Forum statistics

Threads
1,078,137
Messages
5,338,458
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top