# Nested IF/AND with multiple columns

Hey everyone! Thanks so much for all the help you’ve given, I’m still learning and this community has made it a great experience!

I think what I’m trying to do can be solved with nested IF statements, just can’t seem to find the definitive answer looking through old posts. Here’s what I want to do.

I want to calculate the number of hours it takes to do a certain job for a certain employee, where each employee takes a different amount of time for that job.

For example, Employee 1 takes 4 hours to do Task 1, but Employee 2 takes 3 hours to do the same task. There are a bunch of employees and a bunch different tasks, and the hours are different for each employee. Is there a not so messy way to do this?

Thanks so much for the support!

EDIT: this is the formula that I have that works for one Employee and one task

IF(
AND(
{Employee} = “Employee 1”,
),
“4”
)

Where it will show 4 hours for Employee 1 to do task 1.

Where I get confused is how to nest a whole lot of these to cover all possible coniditions. Basically, for each employee, there could be any one of 8 tasks assigned, and the hours for each employee differ for each task.

If there is a much easier way to do this, I’m all ears!

Sounds like you need a pivot table. Complete the grid as you have it in the image and then use the pivot table app to do the sums by task by employee.

1 Like

There is. It’s called the `SWITCH()` function. It’s a much more compact method of generating output based on specific input. The basic format looks like this:

``````SWITCH(
INPUT_SOURCE,
OPTION_1, OUTPUT_1,
OPTION_2, OUTPUT_2,
OPTION_3, OUTPUT_3,
...,
OPTIONAL_DEFAULT_OUTPUT
)
``````

If `INPUT_SOURCE` matches `OPTION_1`, then the function returns `OUTPUT_1`; if it matches `OPTION_2`, then `OUTPUT_2` is returned, and so on. If none of the comparison options are matched, it returns the `OPTIONAL_DEFAULT_OUTPUT` if one has been provided, or nothing if not.

In this case, I can see the formula being a series of nested `SWITCH()` functions:

``````SWITCH(
Employee,
"Employee 1", SWITCH(
...etc
),
"Employee 2", SWITCH(