Feb 18 2020 11:13 AM
I am trying to write a IFS statement using a drop down box to select a product to generate a commission amount to fill into the box.
So I have 4 products lets say they are auto, home, life and accident. For employees enrolled less than a year you get a commission of 5, 10, 7 and 8 respectively. For any employee enrolled over a year, the commissions are 2, 3, 0 and 4 respectively.
I know how to build an IF statement with AND for one product, but how do I build it for those multiple scenarios with with use of the drop down box?
Been looking all over the internet and not finding much.
Thank you.
Feb 18 2020 12:05 PM
If product in A1 and enrollment in B1
formula could be
=LOOKUP(A1,{"accident","auto","home","life"},IF(B1<1,{8,5,10,7},{4,2,3,0}))
But better not to hardcode constants, put them in ranges instead.