Vlookup based on contents of a cell

[geordie_ben]

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

This is how the data is presented:

[TABLE="class: wikitable sortable jquery-tablesorter"]
<thead>[TR]
[TH="class: headerSort, width: %2221%22, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Postcode area[/TH]
[TH="class: headerSort, width: %2221%22, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Postcode districts[/TH]
[TH="class: headerSort, width: %2221%22, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Post town[/TH]
[TH="class: headerSort, width: %2221%22, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EAECF0]#EAECF0[/URL] , align: center"]Former postal county[/TH]
[/TR]
</thead><tbody>[TR]
[TD]AB[/TD]
[TD]AB10, AB11, AB12, AB15, AB16,
AB21, AB22, AB23, AB24, AB25,
AB99^non-geo[/TD]
[TD]Aberdeen[/TD]
[TD]<small style="font-size: 11.9px;">(Aberdeenshire)</small>[/TD]
[/TR]
</tbody>[/TABLE]
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]

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

Excel 2013/2016
	A	B
1	Full postcode	Postcode area
2	AB22 0AA	AB
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)-1)

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

 

[geordie_ben]

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]

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.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Postcode area	Postcode districts	Post town	Former postal county		Full postcode	town
2	AB	AB10, AB11, AB12, AB15, AB16, AB21, AB22, AB23, AB24, AB25, AB99non-geo	Aberdeen	(Aberdeenshire)		AB22 0AA	Aberdeen
Sheet1

Cell Formulas
Range	Formula
G2	=VLOOKUP("*"&LEFT(F2,FIND(" ",F2&" ")-1)&"*",B:C,2,0)

 

[geordie_ben]

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:

[TABLE="class: wikitable sortable jquery-tablesorter"]
<tbody>[TR]
[TD]B[/TD]
[TD]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,
B99^non-geo[/TD]
[TD]Birmingham[/TD]
[TD]<small style="font-size: 11.9px;">(West Midlands)</small>[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B49,
B50[/TD]
[TD]Alcester[/TD]
[TD]Warwickshire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B60, B61[/TD]
[TD]Bromsgrove[/TD]
[TD]Worcestershire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B62, B63[/TD]
[TD]Halesowen[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B64[/TD]
[TD]Cradley Heath[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B65[/TD]
[TD]Rowley Regis[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B66, B67[/TD]
[TD]Smethwick[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B68, B69[/TD]
[TD]OLDBURY[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B70, B71[/TD]
[TD]West Bromwich[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B72, B73, B74, B75, B76[/TD]
[TD]Sutton Coldfield[/TD]
[TD]West Midlands[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B77, B78, B79[/TD]
[TD]TAMWORTH[/TD]
[TD]Staffordshire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B80[/TD]
[TD]STUDLEY[/TD]
[TD]Warwickshire[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B90, B91, B92, B93, B94[/TD]
[TD]Solihull[/TD]
[TD]West Midlands[/TD]
[/TR]
</tbody>[/TABLE]
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]

I've done a text to columns, delimited on space to get the first part of the Post Code

Hi, you can try this:

Excel 2013/2016
	A	B	C	D	E	F	G
1	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)
2	B	B49,B50	Alcester	Warwickshire		B75	West Midlands
3	B	B60, B61	Bromsgrove	Worcestershire
4	B	B62, B63	Halesowen	West Midlands
5	B	B64	Cradley Heath	West Midlands
6	B	B65	Rowley Regis	West Midlands
7	B	B66, B67	Smethwick	West Midlands
8	B	B68, B69	OLDBURY	West Midlands
9	B	B70, B71	West Bromwich	West Midlands
10	B	B72, B73, B74, B75, B76	Sutton Coldfield	West Midlands
11	B	B77, B78, B79	TAMWORTH	Staffordshire
12	B	B80	STUDLEY	Warwickshire
13	B	B90, B91, B92, B93, B94	Solihull	West Midlands
Sheet1

Cell Formulas
Range	Formula
G2	=LOOKUP(2,1/(ISNUMBER(SEARCH(F2&",",$B$1:$B$999&","))),$D$1:$D$999)

 

[geordie_ben]

That's worked perfectly, thanks!!

Out of interest, why did you change the formula?

 

[FormR]

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]

Ah, gotcha

I think luckily they're all in alphabetical order.

Thanks for your help