If then Excel Formula Question

Hi Shayner and welcome to the Forum

As an alternative to Willie's Answer, you use a simple (and probably less confusing) VLOOKUP formula, especially if you have a version earlier than Excel 2013 which introduced INDEX and MATCH.

Your bonus step figures in column D are in ascending order so it's enough to set the lookup value, range and return the value from column 4 of that range. (For completeness I set the 4th argument to TRUE - which means Excel doesn't look for a exact match but works on the last value which B11 exceeded - but you can omit it since that's the default for VLOOKUP).

The formula:

=VLOOKUP($B$11,D15:G130,4,TRUE)

returns the uncapped bonus.

To cap that amount, you just need to wrap the Min (minimum) function around that and set the maximum bonus, changes in bold below (and without that 4th argument):

=MIN(VLOOKUP($B$11,D15:G130,4),13000)

If the VLOOKUP formula returns $15,000 say, this formula gives the lesser figure, i.e. $13,000. 

That formula applies to Jenn and Eva but the formula needs to change for Mary (with the additional 1%). If that addition applies to both the bonus and the $13,000 cap, then B17 is:

=1.01*MIN(VLOOKUP($B$11,D15:G130,4),13000)

If the $13000 bonus cap applies still, that becomes:

=MIN(1.01*VLOOKUP($B$11,D15:G130,4),13000)

and she never gets more than $13,000.

Hope this helps. if so, please mark this Answer as Selected. If you prefer Willie's, do that for his instead. Thanks in advance.

Hello Shayner and welcome to the forum,

Firstly, thank you for uploading a sample file; this always help to understand what is wanted.

To get the bonus amount can be achieved using an Index/Match formula. In your case, using your sample file, it would be: =INDEX(D15:G130,MATCH(B11,D15:D130),4) in "B18" and "B19"; except for Mary ("B17") where it would be: =INDEX(D15:G130,MATCH(B11,D15:D130),4) + $B$3*0.01 because she gets an additional 1%.

Here's how it works: "D15:G130" is where the "Index" part looks, "B11" is what "Match" is looking for, "D15:D130" is where "Match" looks for that value, "4" is the column of the "Index" range to get the value from. Added to the formula for Mary is "+ $B$3*0.01" to add 1% to the bonus.

To cap the bonus at 13,000, these formulas are placed inside an "IF" formula as follows: if the bonus looked up is greate than 13,000 enter 13,000 else enter the looked up bonus. In both cases Mary bets an extra 1%.

Mary (B17): =IF((INDEX(D15:G130,MATCH(B11,D15:D130),4))>13000,13100,(INDEX(D15:G130,MATCH(B11,D15:D130),4) + $B$3*0.01))

Jenn (B18) and Eva (B19): =IF((INDEX(D15:G130,MATCH(B11,D15:D130),4))>13000,13000,(INDEX(D15:G130,MATCH(B11,D15:D130),4)))

In the attached file I have put these formulas in B17, B18, and B19 so you can see how it works. Change/add any value in "B5:B10" and you will see the bonuses update accordingly.

If this answers your question please mark my answer as Selected.

Cheers   :-)