Look up postcode and allocate region

[ew80]

Hi all,

I have 2 sales managers who are responsible for different regions in the country. I've created a spreadsheet with every UK postcode and against each postcode have allocated a sales manager. What I'm trying to do is type in a postcode on a different sheet and for Excel to recognise which sales manager that postcode relates to. Is this possible and if so, how do I do it?? Thanks in advance!

[AliGW]

Welcome to the forum.

You will need a VLOOKUP or INDEX MATCH - both very easy.

There are instructions at the top of the page explaining how to attach your sample workbook.

[ew80]

Thanks AliGW!

I've attached an example of the spreadsheet. So on sheet1 I want to be able to manually type in a postcode in C3 and for C4 to automatically be populated using the info in sheet 2.

I hope that makes sense??

[AliGW]

Here you go:

=INDEX('UK Postcode'!$E$2:$E$15,MATCH(C3,'UK Postcode'!$A$2:$A$15,0))

[ew80]

That's absolutely amazing! Thank you so much!!

I've just found out (probably unsurprisingly) that I can't fit all the postcodes on one sheet. Is it possible to achieve the same but over different sheets? I've attached an updated example

I really appreciate your help

[AliGW]

What do you mean? Why do you think you can't fit them all on one sheet? Of course you can, and it will be far simpler if you do. Don't make your job unnecessarily complicated: put them all on one sheet and use filters if you need to view different regions. Nearly 1.5 million rows should be plenty!!!

[AliGW]

Do you really need postcodes for the whole of the UK???

[ew80]

I've reached cell 1048576 and can't go any further, Google tells me there's 1.7 million postcodes in the UK ... typical! I think this may be becoming too difficult a task for what I'm hoping to automate!

[davsth]

Do you really need all postcodes?
https://www.postcodeaddressfile.co.u..._explained.htm
In the country postcodes are made up of

of 124 Areas L, S, DN etc

2,827 Districts L4, S6, DN7 etc

and 9,487 Sectors L4 3, S6 2, DN7 8 etc

Are you really allocating more finely than this? I would be surprised more finely than District or even Area

Otherwise your allocation will take forever! 1.756 million Postcodes. and 29 million addresses

It is possible to match a postcode to the area or sector or district it is part of

[ew80]

Thanks davsth, actually doing it by sector makes it far easier than by full postcode. I've been given very vague instructions around this and have made it more difficult than it needs to be! You've made my life infinitely more easy! Thank you!

[AliGW]

That's the way forward, and i was going to suggest something similar. Let us know if you need any more help.

[ew80]

You have been amazing!! There's one last thing you might be able to help me with if you don't mind? I'm trying to do a secondary calculation using IF AND but as a novice I'm struggling!!

Based on criteria I want to label different customers either KAM, AM or Associate. The criteria is if the region is Scott and the customer has less than 15 stock they are associate, between 15 - 30 they are AM and more than 30 they are KAM. To make things more complicated, the criteria is different again for Rob. Using IF and AND I've got as far as it working for associate for Scott but then when I try to use multiple statements it just returns FALSE ... I know I'm doing it wrong I'm just not sure what I need to do to make it work!

Thank you so so much for all of your help with this!

[davsth]

without knowing the values for Rob it is hard for a full solution

Have a play with cell d14 is matches the last value in j:L where the person is the person of interest and the stock fits to your levels I made some up for rob

[ew80]

davsth you're an absolute angel! This is perfect, thank you so so much!!!!