Category mapping based on multiple criteria in multiple columns

[Jimbozeubuen]

I have a large amount of data with columns for Division, Department, and Account. I want to assign a category to every row of data based on complex criteria in those columns. Here are some examples:

Category is "Absorption" if Division is not 000, Department is either 000 or in the range of 500-599, and Account is in the range of 60000-69999.

Category is "Logistics" if Department is in the range of 700-799 and Account is in the range of 60000-69999 but is not 64600 or 64625.

I can do this with multiple advanced filters but was wondering if there is a way to do this with formulas. Any help would be greatly appreciated!

[theclockmaker]

What you provided is not so complex that it could not be done, if you add more Boolean rules then the formula can get rather large, but it's still possible. This code will work for what you described above so far. It assumes column B is the division, C is the department, and D is the Account; I'm assuming row 2 is the first check and I assume A2 is where this formula would go:

Please Login or Register  to view this content.

because the code above is a bit complex, I'll break it down into the 3 key sections below, but COPY the code above, this code below won't work if you try to paste it for use:

IF( AND( B2<>0, OR(C2=0, AND( C2>=500, C2<=599 )), AND( D2>=60000, D2<=69999 )),"Absorption",
IF( AND( AND( C2>=700, C2<=799 ), AND( D2>=60000, D2<>64600, D2<>64625, D2<=69999 )),"Logistics",
"Other"))

[AliGW]

How is this bit going to work?

IF( AND( B2<>0, C2=0, AND( C2>=500, C2<=599 )

Why do you need the AND clause for C2 if it needs to equal zero?

[Jimbozeubuen]

Thanks theclockmaker, your formula works perfectly. However, it can be difficult to manage if I want to do this for 30 different categories and each of those categories have complex rules like that. I thought about using a UDF to lookup category names based on multiple criteria but couldn't find any good examples online.

[theclockmaker]

I agree, VBA would be best then, it gives you more control and you can add comments.
Something like this:

Please Login or Register  to view this content.

[theclockmaker]

How is this bit going to work?

IF( AND( B2<>0, C2=0, AND( C2>=500, C2<=599 )

Why do you need the AND clause for C2 if it needs to equal zero?

Nice catch, I'll add edit my original post to include the OR operator as well. It should be C2=0 OR between 500-599

IF( AND( B2<>0, OR(C2=0, AND( C2>=500, C2<=599 )), AND( D2>=60000, D2<=69999 )),"Absorption", ...

[AliGW]

No problem!

[Jimbozeubuen]

Thanks theclockmaker, I will use that macro and expand it for additional categories as needed.