Excel Challenge

[Columbo]

Could do with a bit of help as this beyond my ability.

I have a table of 14 Postcode Zones (2.5k lines) which range from 2 to 4 chars as below:

Zone 1	DN1
Zone 1	DN11
Zone 1	DN12
Zone 1	DN2
Zone 1	DN4
Zone 1	DN5
Zone 1	DN6
Zone 1	HD8
Zone 1	S1

We have another sheet where a full delivery postcode is keyed in as per below

POSTCODE
DY4 7AB

What I want to do is take that postcode and find the Zone automatically, the complexity I'm struggling with is the varying number of chars on the initial part of the postcode.

Due to this I can't use LEFT to strip out what I need, the use a VLOOKUP as it won't work everytime.

Any advice would be appreciated.

 

[Sasahara]

Not very elegant and there probably is a better way.

=IF(MID(A1,5,1)=" ",LEFT(A1,4),IF(MID(A1,4,1)=" ",LEFT(A1,3),LEFT(A1,2)))

A1 being the full postcode being checked. So just add in you vlookup as you intended.

So as long as the " " Space is going to be 5th, 4th, 3rd character that should work.

Edit changed the last "LEFT(A1,2)))" was "LEFT(A1,3)))"

 

[Lartyconshayo]

You could use SEARCH function to return the location of the space in the postcode string then pass that to LEFT function to isolate the left half. I prefer index match over vlookup but in this case personal preference I suppose.

 

[Grrrrr]

=INDEX(A:A,MATCH(LEFT(C1,SEARCH(" ",C1)-1),B:B,0))

Assuming zones are in column A, first part of postcode is in column B and the postcode you want to lookup is in cell C1.

 

[adolf hamster]

=INDEX(A:A,MATCH(LEFT(C1,SEARCH(" ",C1)-1),B:B,0))

Assuming zones are in column A, first part of postcode is in column B and the postcode you want to lookup is in cell C1.

this falls down with the code stubs that are for example DN1 1

assuming that your code stubs are only going to be 3 or 5 characters then this expansion of the above code will work:

=IFERROR(INDEX(Sheet1!A1:A9,MATCH(LEFT(Sheet2!A1,SEARCH(" ",Sheet2!A1)-1),Sheet1!B1:B9,0)),INDEX(Sheet1!A1:A9,MATCH(LEFT(Sheet2!A1,SEARCH(" ",Sheet2!A1)+1),Sheet1!B1:B9,0)))

this is done with the zones starting in A1 and going down, the code stubs starting in B1 and going down, and the full postcode being entered in sheet 2 A1 and down.

if you have further varieties of code stubs, such as 6 characters long this wont work (eg DN1 2A) and you'll need to expand again with another iferror formula.

bearing in mind if you have a list of the following:
zone 1 DN1
zone 1 DN1 1
zone 2 DN1 1a
zone 1 DN2

then this formula will return the result of zone 1 for "DN1 1AG" as it'll simply search for the "DN1 1" part and find the first on the list.

there's probably a better way to do this using array formulas but brain be failing me atm, if your data wont suffer that main issue (for example if all DN1 codes are zone 1 regardless of suffix) then tear away.

 

[ecksmen]

Still very early, but if I understand correctly it's the full postcode that is making this hard for you due to the varying length of postcodes and the space?

I'd split all the columns (text to columns based on a delimited value of space).

 

[Columbo]

Cheers for the solution options guys.

I used the below from Grrrr which works perfectly.

=INDEX(A:A,MATCH(LEFT(C1,SEARCH(" ",C1)-1),B:B,0))