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.