Multiple IF Statement based on Multiple Criteria

cmcneillyjci · 09/10/19

I am currently working on automating an "Assigned To" column based on postal codes. There are multiple postal codes options per individual with three individuals. Is there a way to create a (albeit very long) formula to populate? I'm currently working with

=IF(OR([Postal Code]@row = "01xxx-09xxx", IF([Postal Code]@row = "10xxx-19xxx", IF([Postal Code]@row = "20xxx-29xxx", IF([Postal Code]@row = "30xxx-33xxx", IF([Postal Code]@row = "37xxx-39xxx", IF([Postal Code]@row = "49xxx", IF([Postal Code]@row = "98xxx", IF([Postal Code]@row = "99xxx", "Tech A", "Tech B"

In theory the formula will continue with each of the 14 drop down postal codes.

Thanks in advance!

Paul Newcome · 09/11/19

I agree. Using an INDEX/MATCH to pull from a table would be much more efficient.

If you decided to go the route of the long formula though, you would need to adjust your syntax within the OR function. You wouldn't need to repeat the IF statement.

=IF(<strong>OR([Postal Code]@row = 12345, [Postal Code]@row = 54321, [Postal Code]@row = 67890, [Postal Code]@row = 09876)</strong>, "Tech A", "Tech B")

Andrée Starå · 09/11/19

Hi,

I'd recommend using a VLOOKUP or INDEX/MATCH combination instead or do you prefer to use a very long formula?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome · 09/11/19

I agree. Using an INDEX/MATCH to pull from a table would be much more efficient.

If you decided to go the route of the long formula though, you would need to adjust your syntax within the OR function. You wouldn't need to repeat the IF statement.

=IF(<strong>OR([Postal Code]@row = 12345, [Postal Code]@row = 54321, [Postal Code]@row = 67890, [Postal Code]@row = 09876)</strong>, "Tech A", "Tech B")

cmcneillyjci · 09/12/19

If I use the INDEX/MATCH route - is there a way possible to use variables (such as X) because I have such a large range of numbers or would I need to create a table with every postal code in my use case?

Thanks!

Andrée Starå · 09/12/19

Happy to help!

Not sure I follow!

Can you explain further?

Paul Newcome · 09/12/19

You would need a table that specifies each of the numbers you would otherwise be entering into your IF formula. If you can break down the postal codes into sections such as all that start with 12 go to "tech a" and all that start with 13 go to "tech b", so on and so forth, then you would not need a table containing every single postal code.

Using the x as a variable would also not work in the IF/OR statement either unless the data you are looking at actually has the "x" in it.

If you only need to compare to the left two digits of the postal code, then we would need to specify that using a LEFT function instead of "xxx"

How is the data typed out that you are comparing against?

Are you able to provide some screenshots with sensitive/confidential data hidden, removed, or replaced with "dummy data"? That would GREATLY help us in finding a solution that works for you.

Multiple IF Statement based on Multiple Criteria

Best Answer

Answers

Help Article Resources

Categories