Chart Look up

[PFS12]

Good Morning,
I am looking for a little help. I need a formula that kind of follows along with some sales history throughout the year. I would like it to track along as the numbers fill in so I can easily determine a salesman bonus.

In the example below the total sales in F6 (432914.86) should give me a bonus in the matrix of O12 (6291.84) This will change as the total sales change and the salesmen breaks into different tiers. the tiers will be set in column K, so if the number is greater than or equal to the number in K6 their bonus is determined by O6. K7 is O7, and so on.

Basically I need it to look at F6, compare it to column K and figure out where it fits and give me the corresponding number in column O.

Any help would be great!

Formula for Mr. Excel 1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
5						Budgeted Sales to Breakeven	Increase Needed for Incentive (What does the house make)	Total Sales Number Needed	Actual Weighted PPS Generated Gross Margin	Additional Margin made above costs		Incentive (K*N)
6	Total Sales		$ 432,914.86			$ 289,280.00	15.0%	$ 332,672.00	$ 99,801.60			NO BONUS
7						$ 289,280.00	20.0%	$ 347,136.00	$ 104,140.80	$ 17,356.80	0.25%	$ 867.84
8						$ 289,280.00	25.0%	$ 361,600.00	$ 108,480.00	$ 21,696.00	0.500%	$ 1,808.00
9						$ 289,280.00	30.0%	$ 376,064.00	$ 112,819.20	$ 26,035.20	0.750%	$ 2,820.48
10						$ 289,280.00	35.0%	$ 390,528.00	$ 117,158.40	$ 30,374.40	1.000%	$ 3,905.28
11						$ 289,280.00	40.0%	$ 404,992.00	$ 121,497.60	$ 34,713.60	1.250%	$ 5,062.40
12						$ 289,280.00	45.0%	$ 419,456.00	$ 125,836.80	$ 39,052.80	1.500%	$ 6,291.84
13						$ 289,280.00	50.0%	$ 433,920.00	$ 130,176.00	$ 43,392.00	1.750%	$ 7,593.60
14						$ 289,280.00	55.0%	$ 448,384.00	$ 134,515.20	$ 47,731.20	2.000%	$ 8,967.68
15						$ 289,280.00	60.0%	$ 462,848.00	$ 138,854.40	$ 52,070.40	2.250%	$ 10,414.08
Matrix Model

Cell Formulas
Range	Formula
I6:I15	I6	=$D$17
K6:K15	K6	=I6*J6+I6
L6:L15	L6	=K6*$B$17
J7:J15	J7	=J6+5%
M7:M15	M7	=(K7-I7)*$B$17
O7:O15	O7	=K7*N7
N8:N15	N8	=N7+0.25%

 

[Habtest]

Hey PFS12, approximate match in vlookup will do:
=VLOOKUP(F6,$K:$O,5,1)

Please note it works only if column K is downwards ascending, which it is now.