Postcode conundrum

[Mobeedumi]

Within my spreadsheet - Column H - is where a postcode will be entered. If a postcode is entered in column H, I am wanting column I and J to automatically populate the appropriate information and wandering how best to do this. Not sure how to do this or if it will be via a partial vlookup or are there other simpler or more straightforward way to do this? Sheet 1 has a list of all the postcode in the area. Ideally if the first 4 characters of the postcode matches the postcode that is typed in column H, then column I and J will populate with list of locations created by data validation.to one of the areas (e,g, Daventry), the Cell in column I will show as Daventry and the corresponding cell in column J will state whether if this is north or south. If the postcode does not match any of the list of postcodes, then the outcome should be “out of area” in both column I and J.

Outline of excel database - the formula on display relates to column J

All the postcode for the Northamptonshire area below

Below are potential pivot tables which I would hoe would run/work with all of the above.

 

[MaxSmart]

It would be a lot simpler if you restructured your postcode sheet thus (for all post codes);

Column A = Post code
Column B = Area
Column C = Site

Then it is just a simple case of using Vlookup and returning column 2 or 3

Unfortunately I could not install the mini sheet add-in so this is what I did;

Main Sheet

Patient Name	Post Code	Area	Site
Joe Bloggs	ONE	Northhampton	North
Monty Python	TWO	Daventry	South
Jane Doe	THREE	Out of area	False

Post Code sheet

Post code	Area	Site
ONE	Northhampton	North
TWO	Daventry	South

Formulae in main sheet

Area column
=IF(ISNA(VLOOKUP(B2,Pcodes!A:C,2,0)),"Out of area",VLOOKUP(B2,Pcodes!A:C,2,0))

Site column
=IF(ISNA(VLOOKUP(B2,Pcodes!A:C,3,0)),"False",VLOOKUP(B2,Pcodes!A:C,3,0))