Lookup / Array Range / find the value
 

I have seen similiar examples but not that were not using exact matches to
retrieve data. This seems simple and maybe I am over thinking but I have been
unable to crack this nut. I have a table where the first tier sales runs
between 0 and 20k and awards 1K commission. I want to be able to enter the
salse result for various team members and obtain the correct commission
amount based on their sales for the period. How should I complete the
commission formula for this process?

Sales Value Start Sales Value Stop Commission
$0 $20,000 $1,000.00
$20,001 $25,000 $1,100.00
$25,001 $30,000 $1,200.00
$30,001 $35,000 $1,300.00


Sales Commission
$5,000 $0
$25,001 $0
$32,000 $0

Lookup / Array Range / find the value
 

Suppose your data is from A2 to C5 and your lookup value is in A10 then put
this formula =VLOOKUP(A10,$A$2:$C$5,3)

Vijay

"The Hit man" wrote:

I have seen similiar examples but not that were not using exact matches to
retrieve data. This seems simple and maybe I am over thinking but I have been
unable to crack this nut. I have a table where the first tier sales runs
between 0 and 20k and awards 1K commission. I want to be able to enter the
salse result for various team members and obtain the correct commission
amount based on their sales for the period. How should I complete the
commission formula for this process?

Sales Value Start Sales Value Stop Commission
$0 $20,000 $1,000.00
$20,001 $25,000 $1,100.00
$25,001 $30,000 $1,200.00
$30,001 $35,000 $1,300.00


Sales Commission
$5,000 $0
$25,001 $0
$32,000 $0

Lookup / Array Range / find the value
 

Since you have the list sorted try LOOKUP() as below

=LOOKUP(25001,A1:A5,C1:C5)

--
Jacob (MVP - Excel)


"The Hit man" wrote:

I have seen similiar examples but not that were not using exact matches to
retrieve data. This seems simple and maybe I am over thinking but I have been
unable to crack this nut. I have a table where the first tier sales runs
between 0 and 20k and awards 1K commission. I want to be able to enter the
salse result for various team members and obtain the correct commission
amount based on their sales for the period. How should I complete the
commission formula for this process?

Sales Value Start Sales Value Stop Commission
$0 $20,000 $1,000.00
$20,001 $25,000 $1,100.00
$25,001 $30,000 $1,200.00
$30,001 $35,000 $1,300.00


Sales Commission
$5,000 $0
$25,001 $0
$32,000 $0

Lookup / Array Range / find the value
 

As I indicated in my prelude you cannot use 25001 in the formula as this must
represent random values that will not always be an exact matc for column A. A
value between 0 and 20000 should return the answer 1000from column C. A value
between 20001 and 25000 should return 1100 from column C. Exact matches are
easy but if the actual value = 22500 it is between 20001 and 25000 so should
return 1100 from C. That I have not been able to accomplish. Thanks

"Jacob Skaria" wrote:

Since you have the list sorted try LOOKUP() as below

=LOOKUP(25001,A1:A5,C1:C5)

--
Jacob (MVP - Excel)


"The Hit man" wrote:

I have seen similiar examples but not that were not using exact matches to
retrieve data. This seems simple and maybe I am over thinking but I have been
unable to crack this nut. I have a table where the first tier sales runs
between 0 and 20k and awards 1K commission. I want to be able to enter the
salse result for various team members and obtain the correct commission
amount based on their sales for the period. How should I complete the
commission formula for this process?

Sales Value Start Sales Value Stop Commission
$0 $20,000 $1,000.00
$20,001 $25,000 $1,100.00
$25,001 $30,000 $1,200.00
$30,001 $35,000 $1,300.00


Sales Commission
$5,000 $0
$25,001 $0
$32,000 $0

Lookup / Array Range / find the value
 

Thanks for pointing out this solution. I knew I had missed something simple.
I swear I tried this and I had a problem but I cannot recreate the problem
tonight and this is working for all my test data. i will test my actual rate
charts tomorrow but for now this looks good to go.

"Vijay" wrote:

Suppose your data is from A2 to C5 and your lookup value is in A10 then put
this formula =VLOOKUP(A10,$A$2:$C$5,3)

Vijay

"The Hit man" wrote:

I have seen similiar examples but not that were not using exact matches to
retrieve data. This seems simple and maybe I am over thinking but I have been
unable to crack this nut. I have a table where the first tier sales runs
between 0 and 20k and awards 1K commission. I want to be able to enter the
salse result for various team members and obtain the correct commission
amount based on their sales for the period. How should I complete the
commission formula for this process?

Sales Value Start Sales Value Stop Commission
$0 $20,000 $1,000.00
$20,001 $25,000 $1,100.00
$25,001 $30,000 $1,200.00
$30,001 $35,000 $1,300.00


Sales Commission
$5,000 $0
$25,001 $0
$32,000 $0