Help defining what region an entry is for (Vlookup?/IF?/IFOR?)

[Coolfridgeguy]

Hi there,

I am trying to create a tracker for my work, I work co-ordinating different types of repair staff around the UK. I have to make a function that allocates what region of the country an entry is for, there are 25 locations and they are spread so;

Region 1 : 9 Locations
Region 2 : 9 Locations
Region 3 : 3 Locations
Region 4 : 3 Locations
Region 5 : 1 Location

I have to have a cell that says what region the entry is for based on one of the location names, I can create a separate table and use VLOOKUP but I'm trying to keep this to one sheet. I have been trying a very large nested IF function but it says there are too many arguments (like my marriage eh guys?!?! Oh snap!)

=IF((H81="Hoole",H81="Manchester",H81="Liverpool",H81="Leeds".....etc etc

I have toyed if IF OR functions but excel comes back saying "too many arguements" again

If anyone has any ideas I'd really appreciate it!

 

[steve the fish]

Create table like this:

Hoole	Region 1
Manchester	Region 1
Liverpool	Region 1

<tbody>
</tbody>

Then lookup H81 from the table.

Would that work?

 

[Coolfridgeguy]

like i said I can create one and make a VLOOKUP but I want to keep things to one sheet it needs to be regularly emailed and kept as small as possible, I find if there are to many sheets people dont read them all, I want to keep it on one page, is there a way of keeping the data on a table that size into a function?

 

[steve the fish]

So instead of using a lookup table you want to do it by formula?

It will be harder to maintain but this should work

=IF(OR(H81="Hoole",H81="Manchester",H81="Liverpool",H81="Leeds"),"Region 1",IF(OR(H81="Something",H81="Something Else"),"Region 2",IF(OR(H81="Something",H81="Something Else"),"Region 3",IF(OR(H81="Something",H81="Something Else"),"Region 4",IF(OR(H81="Something",H81="Something Else"),"Region 5","ERROR")))))

 

[Coolfridgeguy]

I usually get the too many arguments warning ill give it a shot

 

[steve the fish]

That will be just a comma in the wrong place or something like that. That formula above works fine. I tested it before posting.

 

[Coolfridgeguy]

I am only getting region 1 with that one, the rest are coming up #NAME?​

 

[Coolfridgeguy]

that still a wrong comma? do i just need to search the syntax?

 

[steve the fish]

Name? Can you post the formula you are using?

 

[Coolfridgeguy]

=IF(OR(H2="location1",H2="location2",H2="location3",H2="location4",H2="location5",H2="location6",H2="location7",H2="location8",H2="location9"),"Region 1",IF(OR(H2="location10",H2="location11",H2="location12",H2="location13",H2="location14",H2="location15",H2="location16",H2="location17"),"Region 2",IF(OR(H2="location18",H2="location19",H2=location20),"Northern Ireland",IF(OR(H2="location21",H2="Mercury",H2="location22"),"Region 3",IF(OR(H2="location23"),"Isle of Man","ERROR")))))

I had to take out the location names (bosses orders)