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 

0
Utf
5/18/2010 4:25:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
884 Views

Similar Articles

[PageSpeed] 9

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 
> 
0
Utf
5/18/2010 5:36:03 AM
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 
> 
0
Utf
5/18/2010 11:30:01 AM
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 
> > 
0
Utf
5/24/2010 4:02:01 AM
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 
> > 
0
Utf
5/24/2010 4:34:01 AM
Reply:

Similar Artilces:

GP- Promise to Pay Reporting by Date Range
I wish to build a query that reports action type, Promise to Pay, by customer within a defined timeframe. In the attached test transaction, I report one transaction with an action date within this timeframe. When I run the query, it prompts me to check an action type when the action type is already built into the query? Why am I required to select an action type? I built query includes two transactions with Promise to Pay, Action Promised. However, one transaction is outside the nominated action query date. I report the same query where I selected a note range to 9/30/2008 and the s...

Lookup in DataGridView? Any Progress? Worth Trying?
In a DataGridView, is it possible to have a column that manages user input by providing a "drop down" list of values the user can choose from, with those values either being "spelled out" in code or read from another db.table.column? If so, does anyone know where I might find instructions on how to set that up? I've seen lots of old posts on this topic, but nothing necessarily specific to the latest incarnation of the DataGrid, the DataGridView. Is it worth trying to pop a List Box on the ColEdit event, let the user "select" from the List Box, a...

Find text in another workbook and paste if found match
Hi! Please need help. I dont know if it possible but... I have 1 workbook sheet with formulas and values that i want to find and paste in another workbook by run a macro. in the workbook1, select the cell with value that i want to paste to workbook2 if the text that i want to find in the workbook2 its found Example select and copy [workbook1]sheet1!k1 (the cell with the value that i want to paste) activate [workbook2]sheet1!a1 then find the text "article 12 ..." if it found select it and move cursor 2 cells right and then paste te value of [workbook1]sheet1!k1 return to [work...

countif(Range,criteria) question
For 'Range' i would like the intersect of the row in which the function resides and the adjacent, named 6 x 6 range. I,ve tried =Countif(MyRng Row(), MyCrit) but no joy Please advise, Thanks Use offset function like this =COUNTIF(OFFSET($A$1,0,1,1,6),">0") The offset parameters are as follows Offset(Rows offset, column Offset, Number of Rows, Numbeer of columns) I put the formula into A1 and the function uses a range starting one column to the right of A1 with a size of 1 row and 6 columns. -- joel ------------------------------------------...

select range in column, calculate on range plus 3 columns, output in range plus 7 columns
Dear expert, I am trying to write code for the following problem; so far without succes. Can you help? First, sort on column A then select the range in column A based on the same name (e.g. Aa). Second, calculate the average of the corresponding range (rows) but 3 columns to the right, and Third put the average of that range in column 7 (the actual output of the macro): columns: A B C D E F G Aa 4 average of range column D: 5.7 Aa 3 average of range column D: 5.7 Aa 10 average of range column D: 5.7 Bb 2 ...

reference a range based on a cell value
I have a sheet where row 1 has names and the rest of the columns numbers. for example harry, bill, ann, kathy, and sparky A program that I can not change pushes data into the spreadsheet. I'd like to be able to calculate row totals for defined groups of names. one day the columns might contain harry, bill, ann, kathy and sparky but the next day it might contain harry, sam, bill, sparky, ken The problem is as the list of names changes columns get added or taken out of the sheet. How can I set up formulas to compute the sum of rows for say harry, bill, and sparky without having to re...

Please Help.....need help with P-Value
Please help, I need to find a P Value on Excel. How do i do it? needed for some statistic problems. GOF,TOI,Sign Test, Run Test, Anova etc. Thanks, CM ...

AVERAGE cells on another worksheet when the range is calculated
I am trying to average a range of cells on the main worksheet of my workbook, but the cells to be averaged are on another worksheet in my workbook where the range of cells to be averaged is calculated from an input cell on the main worksheet Huh?? That is a very confusing paragraph. I think you want something like this: =AVERAGE(OFFSET('Some sheet'!A1,0,0,5,0)) Where 5 is telling the formula to average cells A1:A5. By placing different variables within the OFFSET function you can control which cells you want to average. -- Best Regards, Luke M *Remember to cli...

How can I find what is causing this error
What typically causes the "Microsoft Excel has encountered an error an needs to close, etc, etc," error and crashes Excel? IF there is typical cause -- Kevla ----------------------------------------------------------------------- Kevlar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=723 View this thread: http://www.excelforum.com/showthread.php?threadid=26696 hi, that is general protection fault or GPF. it is cause by various things. Post again under Crashes & GPFs. >-----Original Message----- > >What typically causes the "Micr...

extracting values out of a list using formulars
hi there, i've got the following problem: i've got a list of values (text). this list might contain a value several times. i want to extract the values of this list using a formular. multiple occourances of one value must only be extracted once. the extracted list should be created automatically only using formulars and no macros. here is an example of what a want to do: the user enters values in the cells A1 to A5 and the speedsheet should print out A8 to A10. ------| A | B | 1 | val1 | | 2 | val2 | | 3 | val2 | | 4 | val3 | ...

HTML Web Site Row Range Question
When posting a excel XT spreadsheet to the web the total range of rows are not displayed except by the verticle scroll bar on the spreadsheet. What I want to do is to have the entire spreadsheet on the web site page and use the browser scroll bar. The reason is for not only viewing but printing as well. Thank you in advance for any tips you might have. ...

Lookup Functions #2
When using vlookup, how would I get to the next line in a series? want to pull in specific cells based on broad criteria. For example in market 501, I want to pull in all the addresses and stores in tha market-if I use vlookup it will only pull the first one it comes to-i there a "next "function that will skip the first and pull in the secon and third ect. Or is there a better way to get at this. Thank -- jcookse ----------------------------------------------------------------------- jcooksey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1485 Vi...

How do I insert a formula into chart source data values field box?
My ultimate aim is to build a chart that refers to values in a range of cells held in a row on another worksheet. Normally this would be straightforward by manually editing the source data in the chart and pointing to the range of cells. However, over time the range of cells used to provide the values for the chart will change and I am looking for a method that will do this automatically. I thought if I could produce a formula that identifies the cells when the change I could simply paste that formula into the Chart's source data values field. Of course, I've tried it but it will not ...

Exact lookup questions
These questions are more out of curiosity than anything else. I have been experimenting with VLOOKUP with an exact match, and would like some background to what seems to me to be curious behaviour. In cells A1:A4 I have 0.1 0.5 =1/3 0.4 The =1/3 displays as 0.333333333 in the cell and as =1/3 in the formula bar. In C1 I have: =VLOOKUP(D1,A1:A4,1,FALSE) In E1 I have =D1-A3, formatted as scientific with 2 decimal places. If I enter =1/3 in cell D1 I get a match and E1 displays 0.00E+00, as expected. Now for the curious bits. If I enter 0.333333333333333 in D1 (15 3's) then E1 knows ...

Integer maximum value
I have define a field type as integer. I tried to put a value 120,000 but it seems exceeds the value. Hi Alan, An integer field stores numbers from -32,768 to 32,767. A long integer field stores numbers from -2,147,483,648 to 2,147,483,647. So, just change the field size from integer to long integer, and you should be OK. Help on Field Size gives the specifications for all the data types available. HTH, Rob "Alan T" <alanpltseNOSPAM@yahoo.com.au> wrote in message news:O9n5rldkHHA.4048@TK2MSFTNGP02.phx.gbl... >I have define a field type as integer. > I tried...

Using LastCell value in VBA code
Good Morning. Using Office 2003 I am trying to: 1) Locate the last cell imported data. 2) Copy a formula in G2 for all G cells to the end of data (G1 contains field names) To get the last cell of data, I used Edit>GoTo>Special>Last Cell In the code below, I attempted to insert the value of the last cell into a Range("G1:xxxxx).Select to do a paste of G2.formula. My attempt did not work. I am sure there is a better way. Help please! TIA Dennis Sub FindLastCell() ' Partial code below [OK] Selection.SpecialCells(xlCellTypeLastCell).Select [Bad] Range("G3:&...

dragging formula copies values instead
hi, i have been experiencing a strange occurence in my workbook which i replete to the hilt with macros. when i enter a formula in a cell and drag it down, it copies the value down the column, instead of the formula. that is, if the value of th formula for a particular row is 225, it copies 225 down the whol column irrespective of what the result of the formula may be for an other row. i have checked and dont seem to find a reason for it. however, when i click F9 to recalculate, it shows the recalculates th formula and shows the correct values. could there be a logical explanation and hen...

Permission to fill in cell when another cell has a value
How to set that in a cell B1 it is allowed to fill in data only when Cell A1 has any or has a certain value . I guessed via Data Validation, but failed. Bart Excel 2003 Select cell B1, and choose Data>Validation For Allow, choose Custom In the Formula box, enter: =A1<>"" Remove the check mark from Ignore blank Click OK AA Arens wrote: > How to set that in a cell B1 it is allowed to fill in data only when > Cell A1 has any or has a certain value . I guessed via Data Validation, > but failed. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.ht...

#VALUE! on correct formulas
I am using 'vlookup' and 'match' functions that result in #VALUE!, but when I hit function wizard, it shows the correct number value I am looking for and expecting in the result area. Apparently my syntax, etc. is accurate. Why, then, does #VALUE! show up when everything is ok? thanks, mike allen mike allen wrote... >I am using 'vlookup' and 'match' functions that result in #VALUE!, but when >I hit function wizard, it shows the correct number value I am looking for >and expecting in the result area. Apparently my syntax, etc. is accurate. >...

Lost all outlook data! Where can i find it back
I have a laptop computer with W2k and i have officeXP in it which installed over Office2000. It have all worked OK for some time, until today i just lost everything out of Microsoft Outlook. Lost contacts, inbox, outbox, all mail accounts, notes........... Is it possible to find it in some file somewhere on harddisc? There must be some file outlook keeps all data in. Do a search for a .pst file. This is the file outlooks keeps all the contacts / emails and what knots in. Thanks "Logni� Stormsson" <anonymous@discussions.microsoft.com> wrote in message news:02ea01c3c8ed$4...

Advanced Find 10-03-06
Hi All Is it possible to display the results of a saved query in an iframe ? If so how ? I would have thought it would be just a matter of creating the url and passing the parameters in, but i cant find the url Any help much appreciated. Thanks Luke Have you tried using the developer toolbar for IE? It is very helpful in finding these kinds of things. I would look in the CRM Web directory and see if I could find the advanced find aspx and try to figure out the url that way. Any luck? Gustaf Westerlund "Luke" wrote: > Hi All > > Is it possible to display the...

Returning a value froma dialog
Hi, I have an app that pops-up a dialog to ask for the username / password. When the user clicks the login button in the dialog, my CLogin class (derived from CDialog) attempts to login the user, if it suceeds, i need to return OK and close the Dlg. If it fails, i need to return the error code (-1, -3 etc..) in my app: CLogin loginDlg; int retCode = loginDlg.DoModal(); if(retCode != LOGIN_OK) { ... } How do i make my dialog return a value? CDialog::EndDialog( int nResult ); "JoeB" <joe____A_A_A_A_A_ANoSpamPleaseThankyou_@kybert.com> wrote in message news:Ok$...

FIND equivalent in an SRS Expression
I need to show/hide a line in a report depending on if more than one value is chosen in a multi-select parameter. I know how to determine if that is possible. I just look for a comma in the following formula… ="Provider Type: " & Join(Parameters!ProviderType.Value, ", ") My only issue is within SRS Expression Syntax, how do I look for the comma within an SRS expression? What is the formula to look for a value in a string? Any assistance is extremely appreciated. I figured it out using the InStr expression. The final formula was =iif(...

Selection/Range different in Word 2010 ???
I'm testing my code on Office 2010 (latest Beta). Most everything works, except i'm hitting a strange problem in one of my functions that inserts text (piece by piece) into a doc. I'm calling this function from another function that takes a string with markup in it, e.g. "Name, G. (2002). <i>Title of paper</i>. Santa Cruz, CA." and turning it into a paragraph. I "walk" through the string, breaking it into pieces that have or don't have markup. And then I insert each piece at the end of a paragraph; if the piece has markup, then th...

Delete all Named Ranges except PrintArea
I have the following function that just needs a slight tweak. I need t delete all Named ranges EXCEPT for PrintArea. What do I need to do t adjust this Macro so that it does not delete the Named Range fo PrintArea. Thanks Sub DeletePhantomRangeNames() For Each nm In ActiveWorkbook.Names nm.Delete Next nm End Su -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26772 Doc, Dim nm As Name For Each n...