How do I Return a Value Using Multiple Criteria?

[eed]

I need to return a value (a $AMOUNT) based on the following information:
TITLE, LEVEL 1 2 3 or 4, and a percentage range.

Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a percent
in the [PERCENTAGE RANGE, ie: 5-6.99%], then they receive a certain bonus
[PAYOUT AMOUNT] based on their [BONUS LEVEL].

The static information is currently set up in a chart as follows:

TITLE/BONUS LEVEL/INCREASE OVER SALES PLAN % RANGE/PAYOUT AMOUNT
Store Manager 1 5.00%-6.99% $600.00
Store Manager 1 7.00%-8.99% $800.00
Store Manager 1 9.00%-10.99% $1,000.00
Store Manager 1 11.00%-1000.00% $1,200.00
Store Manager 2 5.00%-6.99% $400.00
Store Manager 2 7.00%-8.99% $600.00
Store Manager 2 9.00%-10.99% $800.00
Store Manager 2 11.00%-1000.00% $1,000.00
Associate Manager 1 5.00%-6.99% $400.00
Associate Manager 1 7.00%-8.99% $500.00
Associate Manager 1 9.00%-10.99% $600.00
Associate Manager 1 11.00%-1000.00% $700.00
Associate Manager 2 5.00%-6.99% $300.00
Associate Manager 2 7.00%-8.99% $400.00
Associate Manager 2 9.00%-10.99% $500.00
Associate Manager 2 11.00%-1000.00% $600.00

The info above is in SHEET 2. The ‘TITLE’ heading is in A1.

The variable/monthly info is in SHEET 1 and contains the info that tells me
who is entitled to receive a bonus, but ultimately I need to return the $
amount (From the ‘PAYOUT AMOUNT column) they should receive based on the
chart above.

The ‘TITLE’ column heading is A1, ‘% OVER SALES PLAN heading is in B1, etc.

TITLE % OVER SALES PLAN Bonus Level Bonus Amount
Store Manager 5.00% 2 ?
Assistant Mgr 11.00% 4 ?


Help? Thanks!!

 

[Sean Timmons]

If you can take the increase range and aplit into two columns, you can use
SUMPRODUCT

=SUMPRODUCT(Sheet1!$A$2:$A$500=$A2)*(Sheet1!$C$2:$C$500<=$B2)*(Sheet1!$D$2:$D$500>=$B2)*(Sheet1!$B$2:$B$500=$C2)*$E$2:$E$500)

Assumes your table is on Sheet1 from columns A - E (remember, the %'s are
now in 2 columns).

If column a matches your result sheet's A cell, begin range of % is less
than or equal to bonus rate, end range is greater than or equal to bonus rate
AND level matches, then return the payout amount listed.

Please note, it does require the same cell range in all criteria and cannot
be an entire column (Cannot use $B:$B, must be $B$2:$B$500 or some such range)