Home |
Search |
Today's Posts |
#1
|
|||
|
|||
An in between lookup function
I am looking for a function that will allow me to compare a sales number to
lower column 1 and upper column 2, and return to me a percentage that's in column 3. This will be used for commision percentage depending on $'s of sales. |
#2
|
|||
|
|||
This formula will lookup a value that is greater or equal to the lookup value
=INDEX(C1:C20,MATCH(SMALL(B1:B20,COUNTIF(B1:B20,"< "&E1)+1),B1:B20,0)) where C1:C20 is the percentage and B1:B20 the upper level values. You might also want to have a look at this if the commisions are variable http://www.mcgimpsey.com/excel/variablerate.html Regards, Peo Sjoblom "Duncan Help" wrote: I am looking for a function that will allow me to compare a sales number to lower column 1 and upper column 2, and return to me a percentage that's in column 3. This will be used for commision percentage depending on $'s of sales. |
#3
|
|||
|
|||
Peo Sjoblom wrote...
This formula will lookup a value that is greater or equal to the lookup value =INDEX(C1:C20,MATCH(SMALL(B1:B20,COUNTIF(B1:B20," <"&E1)+1),B1:B20,0)) where C1:C20 is the percentage and B1:B20 the upper level values. You might also want to have a look at this if the commisions are variable .... If the col A values in the row below are equal to the col B values in the row above, e.g., ___0 1000 5% 1000 2000 8% 2000 5000 11% 5000 10000 14% and if the col A and B values were sorted in ascending order, it'd be a whole lot simpler to use =VLOOKUP(E1-0.000001,A1:C20,3) If the col A values in the row below are equal to one more than the col B values in the row above, this could be simplified even further to =VLOOKUP(E1,A1:C20,3) "Duncan Help" wrote... I am looking for a function that will allow me to compare a sales number to lower column 1 and upper column 2, and return to me a percentage that's in column 3. This will be used for commision percentage depending on $'s of sales. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup & Lookup function error | Excel Worksheet Functions | |||
LOOKUP FUNCTION WITH SUMS ABILITY | Excel Discussion (Misc queries) | |||
Lookup Function | Excel Worksheet Functions | |||
Vector lookup function | Excel Worksheet Functions | |||
Another Lookup function, please | Excel Worksheet Functions |