Multiply formula with multiple criteria

[LisaMarie1981]

I need a formula that allows me to calculate commission based off of several scenarios. If they have less than 3 deals (let's say in cell A1 to make simple) than I need to multiply their revenue (in cell A2) by .75, if over 3 deals, AND their revenue is under $75K then multiply by .80, if over 3 deals, AND their revenue is between 76K-100K then multiply by .85, if over 3 deals, AND their revenue is over $100K then multiply by .90. I've tried using IF's, PRODUCT formulas, can anyone help me with this please?

TOTAL DEALS	TOTAL REVENUE FOR AGENT
3	$                44,250.00

[#2 · Accepted solution]

@LisaMarie1981 

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

[#3]

@Hans Vogelaar 

AMAZING!!! Thank you so much! That was driving me crazy!

[#4]

@Hans Vogelaar How would you adjust this if you wanted to grab the data/numbers in bold from actual cells instead so that if we ever had to change the percentages or numbers it would automatically update? Like a plug and play scenario I guess. Is that possible?

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

[#5]

@LisaMarie1981 

See the attached sample workbook. I used two cells for the 3 and 0.75, and a table for the other values.

You can edit the values, and if necessary expand the table. The formula will take the changes into account.

[#6]

This is fantastic! Thank you so much!