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!!

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)

"eed" wrote:

> 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!!
>