Excel If for one condition with multiple criteria

[Caly]

Hi I have a formula using an =IF(AND with the INDEX MATCH function, where the leading statement is the same for all other logic nested IF statements and want to use that leading criteria once to apply for all other logic statements. How do I do that? The same statement is checking to see if there are sales greater than $100 and that is checking against 3 criteria and it is basically 4 statements but trying to not have to repeat the “if(and(sales>=$100” into each statement. Please help.


Below is the formula.

formula:
=if(and(sales>=$100,margin>=index(Starting Cost:Floor percent,match(cost of the item,Floor percent,(-1)),3),margin<=index(Starting Cost:Ceiling percent,match(cost of the item,Ceiling percent,(-1)),4)),sell price,

If(and(sales>=$100,margin<index(Starting Cost:Floor percent,match(cost of the item,Floor percent,(-1)),3)),Cost/(1-index(Starting Cost:Floor percent,match(cost of the item,Floor percent,(-1)),3)),

If(and(sales>=$100,margin>index(Starting Cost:Ceiling percent,match(cost of the item,Ceiling percent,(-1)),4)),Cost/(1-index(Starting Cost:Ceiling percent,match(cost of the item,Ceiling percent,(-1)),4)),sell price


below is the translation:
1. If the margin is greater than the floor and less than the ceiling then it is good And use the sell price as it is
2. If the margin is too low then it raises it to the floor
3. If the margin is too high it brings it down to the floor
4. If all statements not true then the resulting answer will be the sell price as it is.

 

[Caly]

Thank you