If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|