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:

Postcode area	Postcode districts	Post town	Former postal county
AB	AB10, 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]

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:

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	<small style="font-size: 11.9px;">(West Midlands)</small>
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]

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