Using Vlookup with Offset

[howard]

I have a table with data and need to calculate the commission payable based on the units achieved.


If for example a person achieves unit sales of say 615, the calculation should be =(615/700)*1000 -see sample data below


I need formula that if the unit sales is higher than the amount looked up in the table, it will take the unit sales below the number looked up and divide this by the unit sales in the next row in the table and multiply this by the value in the second column in the table


I my sample 615 was the unit sales achieved , amount in the table is 600, so the next highest unit is 700 and the value applicable is 1000, so formula is 615/700 x 1000


I would possible need a Vlookup with an offset function, but cannot get it to work


It would be appreciated if someone could assist me

Book1
	A	B
1	0	500
2	600	800
3	700	1000
4	800	1500
5	900	6000
6
7
8
9
10	achievement	615
11
12	Vlookup	800
13
14
15	Calcl should Be	878.57
16
17	Using Vlookup with Offset
18
19
20
Sheet1

Cell Formulas
Range	Formula
B12	=VLOOKUP(B10,A1:B5,2,TRUE)
B15	=B10/A3*B3

 

[bosco_yip]

Maybe,

B12, enter :

=B10/AGGREGATE(14,6,A1:A5/B1:B5/(A1:A5>=B10),1)

Regards
Bosco

 

[howard]

Thanks for the help Bosco . Your formula works perfectly Please explain how the aggregate formula works and what aggregate(14,6 means

 

[inactiveuserps07]

Howard, it looks like you've found your solution in Bosco's formula. Here's another (for those who may have an older version of Excel):

=B10/INDEX(A1:A5,MATCH(B10,A1:A5,1)+1)*INDEX(B1:B5,MATCH(B10,A1:A5,1)+1)

However, given your setup, I don't see when any value in B10 would wind up being multiplied by the value in B1 (i.e., 500). It leaves me wondering why there is a value in B1 at all.

 

[bosco_yip]

Thanks for the help Bosco . Your formula works perfectly Please explain how the aggregate formula works and what aggregate(14,6 means

AGGREGATE(14,6,……….

AGGREGATE with function_num 14 >> LARGE function

and, options 6 >> Ignore error values

Further detail please press F1 in checking with Excel helper file.

Regards
Bosco

 

[howard]

Thanks for the help. You are 100% correct that there is no need for a value in B1

 

[howard]

Thanks for the explanation Bosco