=IF((AND(D2>5,C2>10000)),2,1)
=IF((OR(D2>5,C2>10000)),2,1)
Let’s say we want to assign a job level 3 if the employee meets BOTH criteria above, a job level 2 if the employee meets EITHER criteria above, and a job level 1 if the employee doesn’t meet any of the criteria above.
Put this formula in E2 and copy it down to E8
=IF((AND(D2>5,C2>10000)),3,(IF((OR(D2>5,C2>10000)),2,1)))
You can also nest multiple IFs if you want different results based on different
values in the same cell. For this example, let’s say all of the employees
in the East district will be assigned a job level 4, the West ones will
be job level 3, the North ones will be job level 2, and the ones in the
South will be job level 1. Our formula would look like this:
=IF((B2="East"),4,IF((B2="West"),3,IF((B2="North"),2,IF((B2="South"),1,""))))
Note that the value if false is “”, which tells Excel to leave
the cell empty if no match is found in column B for that row. If you delete
the contents of any cell in column B or change it to anything other than
East, West, North, or South, you will see the corresponding cell in column
E would now be empty. Remember, you must have something in your third argument
(value if false) or Excel will simply enter the text “FALSE”
into that cell if it does not find a match.