Hi there, I am quite a novice at making Excel do what I want it to do but suppose that is mainly my lack of logical thinking.


I would like to get the following conditions in an “embedded IF” function. The outcome should indicate the amount of bonus a person gets.
Facts:
Employees in pay scale 1 - 6 get a bonus of € 1.000,—
Employees in pay scale 7 & 8 get a bonus of € 1.500,—
Employees in pay scale 9 & 10 get a bonus of € 2.000,—
=IF(C2<7;1000;IF(C2>8;2000;1500))

But!!! (1) If you are a CEO who receives housing benefits you do not get the bonus and (2) neither do you if you are a MD with a housing benefit and a pay scale higher than 8.

cell A2 will indicate job title (“CEO” - “MD” - or “Other”);
Cell B2 will indicate housing benefit (“yes” or “no”);
Cell C2 will indicate the pay scale (1-10)

Can anyone give me a clue how to solve this?

Is this a start?

=IF(N(C2),IF(AND(A2="CEO",B2="Yes"),"",IF(AND(A2=" MD",B2="Yes",C2>8),"",LOOKUP(C2,{1,1;7,1.5;9,2}))) ,"")

Hi Jeffrey,
Thank you vers Michael. This helpen me to sort my problem.
Kind regards
Yde