Welcome to the community, @Lucy_Evans! :grinning_face_with_big_eyes: My first thought is that you could write a formula that nests various SWITCH()
functions. Here’s a rough example using just a few options:
SWITCH(
LEFT(Postcode & "", 1),
"A", SWITCH(
MID(Postcode & "", 2, 1),
"B", "Salesperson 2",
"L", "Salesperson 1"
),
"E", SWITCH(
MID(Postcode & "", 2, 1),
"C", "Salesperson 1",
"H", "Salesperson 2",
"N", "Salesperson 1",
"X", "Salesperson 7",
"Salesperson 1"
)
)

The first thing we check is the leftmost character in the postcode. If it matches a certain letter, the result depends on the next SWITCH()
level, which checks the second character. With the A postcodes in your sample table, there are only two options, and both have second letters we can pull from. With the E group, there are for with second letters, and one that’s just E on its own, so that E-only option becomes the default if the others don’t match.
Does that make sense?
Hi @Lucy_Evans - unless you are only operating in a few postcodes areas, this approach might not be scalable for you. There are more than 3000 “outer” postcodes in the UK (the first part of the postcode), so that formula could get a bit long if you need to accommodate even a small fraction of these. If you get new salespeople or responsibilities change, then you will have to edit the formula which is fiddly.
Another approach might be to have a reference table of the outer postcodes and assign salespeople to these - like this:

Then, in your main table you can extract the outer part of the postcode - everything up to the space. (This assumes that all of your postcodes have the space!).
The final step is to link it to the postcode table so that you can lookup the salesperson. There’s various ways to do this - copy and paste into a linked field, a script, Zapier for example.
Hi @Lucy_Evans - unless you are only operating in a few postcodes areas, this approach might not be scalable for you. There are more than 3000 “outer” postcodes in the UK (the first part of the postcode), so that formula could get a bit long if you need to accommodate even a small fraction of these. If you get new salespeople or responsibilities change, then you will have to edit the formula which is fiddly.
Another approach might be to have a reference table of the outer postcodes and assign salespeople to these - like this:

Then, in your main table you can extract the outer part of the postcode - everything up to the space. (This assumes that all of your postcodes have the space!).
The final step is to link it to the postcode table so that you can lookup the salesperson. There’s various ways to do this - copy and paste into a linked field, a script, Zapier for example.
FYI, you can download a list of the outer postcodes from here:
https://www.freemaptools.com/download-uk-postcode-lat-lng.htm
