Home

IFS with AND and multiple falses

%3CLINGO-SUB%20id%3D%22lingo-sub-1180356%22%20slang%3D%22en-US%22%3EIFS%20with%20AND%20and%20multiple%20falses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1180356%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20a%20IFS%20statement%20using%20a%20drop%20down%20box%20to%20select%20a%20product%20to%20generate%20a%20commission%20amount%20to%20fill%20into%20the%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%204%20products%20lets%20say%20they%20are%20auto%2C%20home%2C%20life%20and%20accident.%26nbsp%3B%20For%20employees%20enrolled%20less%20than%20a%20year%20you%20get%20a%20commission%20of%205%2C%2010%2C%207%20and%208%20respectively.%26nbsp%3B%20For%20any%20employee%20enrolled%20over%20a%20year%2C%20the%20commissions%20are%202%2C%203%2C%200%20and%204%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20build%20an%20IF%20statement%20with%20AND%20for%20one%20product%2C%20but%20how%20do%20I%20build%20it%20for%20those%20multiple%20scenarios%20with%20with%20use%20of%20the%20drop%20down%20box%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeen%20looking%20all%20over%20the%20internet%20and%20not%20finding%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1180356%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1180465%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20with%20AND%20and%20multiple%20falses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1180465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F562307%22%20target%3D%22_blank%22%3E%40Bradley79%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20product%20in%20A1%20and%20enrollment%20in%20B1%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20696px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171860i86C5C90BF6D977F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(A1%2C%7B%22accident%22%2C%22auto%22%2C%22home%22%2C%22life%22%7D%2CIF(B1%26lt%3B1%2C%7B8%2C5%2C10%2C7%7D%2C%7B4%2C2%2C3%2C0%7D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBut%20better%20not%20to%20hardcode%20constants%2C%20put%20them%20in%20ranges%20instead.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1182913%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20with%20AND%20and%20multiple%20falses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1182913%22%20slang%3D%22en-US%22%3E%3CP%3EPerfect!%20Thank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

 

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.

2 Replies
Highlighted

@Bradley79 

If product in A1 and enrollment in B1

image.png

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.

Highlighted

Perfect! Thank you @Sergei Baklan 

Related Conversations