Adjusting formulas to work with data vallidation drop down lists

[agroeneveld]

See attached spreadsheet.

I had my original formula as follows:

N27 Original Formula:

=IF(M27>0,(IF(K27>0,(IF(M27=1,(IF(K27*2<=500000,ROUNDUP(K27*2,-3)*LOOKUP(AO27,{0,65,70},{1,0.65,0.5}),500000*LOOKUP(AO27,{0,65,70},{1,0.65,0.5}))),(IF(M27=2,(IF(K27*2<=200000,ROUNDUP(K27*2,-3)*LOOKUP(AO27,{0,65,70},{1,0.65,0.5}),200000*LOOKUP(AO27,{0,65,70},{1,0.65,0.5}))))))),"Add Salary")),)

This fomula states that: If M26 has something in it, if the Person is an executive do this, If the person is an employee do this

Instead of having to input a 1 or 2 in Column M, I want to put in a drop down menu which I have in row 25….

How can I adjust the formulas in Columns N & O to work with the new data validation drop down ???

[JBeaucaire]

The simplest edit:

=IF(M26>0,(IF(K26>0,(IF(M26="Executive",(IF(K26*2<=500000,ROUNDUP(K26*2,-3)*LOOKUP(AO26,{0,65,70},{1,0.65,0.5}),500000*LOOKUP(AO26,{0,65,70},{1,0.65,0.5}))),(IF(M26="Employee",(IF(K26*2<=200000,ROUNDUP(K26*2,-3)*LOOKUP(AO26,{0,65,70},{1,0.65,0.5}),200000*LOOKUP(AO26,{0,65,70},{1,0.65,0.5}))))))),"Add Salary")),)