Vlookup from a formula field

Hello,

I would like to ask for help.  Am I able to vlookup from a field?  Here is 
what I'm doing:

- on sheet two I have a lookup field (Column 1 is the percentage, column 2 
is the amount paid if that percentage is reached)

- in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
and the percent to quota in A3 (A2/A1)

In A4 I would like to have the lookup return the paid out amount based on 
the percentage shown in A3, however I am receiving a #N/A.

The only thing I can think of is that I cannot use a vlookup for this type 
of calculation.

Any suggestions on how I may be able to complete this task?

Thanks
Jim
0
Utf
12/8/2009 2:28:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
875 Views

Similar Articles

[PageSpeed] 12

Your table needs to be sorted, and in your VLOOKUP formula you need to
omit the 4th parameter (or set it to TRUE).

It is unlikely that your calculated percentage would exactly equal the
percentages in your table.

If you are still stuck, post examples of your formula and your table.

Hope this helps.

Pete

On Dec 8, 2:28=A0pm, Jim <J...@discussions.microsoft.com> wrote:
> Hello,
>
> I would like to ask for help. =A0Am I able to vlookup from a field? =A0He=
re is
> what I'm doing:
>
> - on sheet two I have a lookup field (Column 1 is the percentage, column =
2
> is the amount paid if that percentage is reached)
>
> - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2=
,
> and the percent to quota in A3 (A2/A1)
>
> In A4 I would like to have the lookup return the paid out amount based on
> the percentage shown in A3, however I am receiving a #N/A.
>
> The only thing I can think of is that I cannot use a vlookup for this typ=
e
> of calculation.
>
> Any suggestions on how I may be able to complete this task?
>
> Thanks
> Jim

0
Pete_UK
12/8/2009 2:38:19 PM
Hi,
try

=VLOOKUP(C1,Sheet2!A:B,2,FALSE)

but I have some questions what if you have the same % more than once, 
vlookup will bring the first amount it found,
is it possible to have same % with dif amounts ?

"Jim" wrote:

> Hello,
> 
> I would like to ask for help.  Am I able to vlookup from a field?  Here is 
> what I'm doing:
> 
> - on sheet two I have a lookup field (Column 1 is the percentage, column 2 
> is the amount paid if that percentage is reached)
> 
> - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
> and the percent to quota in A3 (A2/A1)
> 
> In A4 I would like to have the lookup return the paid out amount based on 
> the percentage shown in A3, however I am receiving a #N/A.
> 
> The only thing I can think of is that I cannot use a vlookup for this type 
> of calculation.
> 
> Any suggestions on how I may be able to complete this task?
> 
> Thanks
> Jim
0
Utf
12/8/2009 2:45:02 PM
Eduardo,

Thanks for the help.  The formula you provided I have tried, it is the one 
returning the error.  Any other suggestions about how to make this vlookup 
work?

Your questions, the same percentage will not occur twice.

Thanks
Jim



"Eduardo" wrote:

> Hi,
> try
> 
> =VLOOKUP(C1,Sheet2!A:B,2,FALSE)
> 
> but I have some questions what if you have the same % more than once, 
> vlookup will bring the first amount it found,
> is it possible to have same % with dif amounts ?
> 
> "Jim" wrote:
> 
> > Hello,
> > 
> > I would like to ask for help.  Am I able to vlookup from a field?  Here is 
> > what I'm doing:
> > 
> > - on sheet two I have a lookup field (Column 1 is the percentage, column 2 
> > is the amount paid if that percentage is reached)
> > 
> > - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
> > and the percent to quota in A3 (A2/A1)
> > 
> > In A4 I would like to have the lookup return the paid out amount based on 
> > the percentage shown in A3, however I am receiving a #N/A.
> > 
> > The only thing I can think of is that I cannot use a vlookup for this type 
> > of calculation.
> > 
> > Any suggestions on how I may be able to complete this task?
> > 
> > Thanks
> > Jim
0
Utf
12/8/2009 2:51:01 PM
Hi Jim,
Is the % in sheet2 calculated or manually entered, or come from a database. 
Check if there is not any blank space at the beginning, you can use this 
formula to remove any blank space and then copy as value on top

=trim(a1)



"Jim" wrote:

> Eduardo,
> 
> Thanks for the help.  The formula you provided I have tried, it is the one 
> returning the error.  Any other suggestions about how to make this vlookup 
> work?
> 
> Your questions, the same percentage will not occur twice.
> 
> Thanks
> Jim
> 
> 
> 
> "Eduardo" wrote:
> 
> > Hi,
> > try
> > 
> > =VLOOKUP(C1,Sheet2!A:B,2,FALSE)
> > 
> > but I have some questions what if you have the same % more than once, 
> > vlookup will bring the first amount it found,
> > is it possible to have same % with dif amounts ?
> > 
> > "Jim" wrote:
> > 
> > > Hello,
> > > 
> > > I would like to ask for help.  Am I able to vlookup from a field?  Here is 
> > > what I'm doing:
> > > 
> > > - on sheet two I have a lookup field (Column 1 is the percentage, column 2 
> > > is the amount paid if that percentage is reached)
> > > 
> > > - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
> > > and the percent to quota in A3 (A2/A1)
> > > 
> > > In A4 I would like to have the lookup return the paid out amount based on 
> > > the percentage shown in A3, however I am receiving a #N/A.
> > > 
> > > The only thing I can think of is that I cannot use a vlookup for this type 
> > > of calculation.
> > > 
> > > Any suggestions on how I may be able to complete this task?
> > > 
> > > Thanks
> > > Jim
0
Utf
12/8/2009 3:00:01 PM
Eduardo,

Sheet Two is manually entered. 

Sheet one is calculated.

I did confirm there are no blanks.

I can't help but think I'm soing something wrong.  Any other thoughts?

Jim

"Eduardo" wrote:

> Hi Jim,
> Is the % in sheet2 calculated or manually entered, or come from a database. 
> Check if there is not any blank space at the beginning, you can use this 
> formula to remove any blank space and then copy as value on top
> 
> =trim(a1)
> 
> 
> 
> "Jim" wrote:
> 
> > Eduardo,
> > 
> > Thanks for the help.  The formula you provided I have tried, it is the one 
> > returning the error.  Any other suggestions about how to make this vlookup 
> > work?
> > 
> > Your questions, the same percentage will not occur twice.
> > 
> > Thanks
> > Jim
> > 
> > 
> > 
> > "Eduardo" wrote:
> > 
> > > Hi,
> > > try
> > > 
> > > =VLOOKUP(C1,Sheet2!A:B,2,FALSE)
> > > 
> > > but I have some questions what if you have the same % more than once, 
> > > vlookup will bring the first amount it found,
> > > is it possible to have same % with dif amounts ?
> > > 
> > > "Jim" wrote:
> > > 
> > > > Hello,
> > > > 
> > > > I would like to ask for help.  Am I able to vlookup from a field?  Here is 
> > > > what I'm doing:
> > > > 
> > > > - on sheet two I have a lookup field (Column 1 is the percentage, column 2 
> > > > is the amount paid if that percentage is reached)
> > > > 
> > > > - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
> > > > and the percent to quota in A3 (A2/A1)
> > > > 
> > > > In A4 I would like to have the lookup return the paid out amount based on 
> > > > the percentage shown in A3, however I am receiving a #N/A.
> > > > 
> > > > The only thing I can think of is that I cannot use a vlookup for this type 
> > > > of calculation.
> > > > 
> > > > Any suggestions on how I may be able to complete this task?
> > > > 
> > > > Thanks
> > > > Jim
0
Utf
12/8/2009 3:25:01 PM
Hi,
I test the formula and it's working for me, for example 2 % are you entering 
it as 0.02 

"Jim" wrote:

> Eduardo,
> 
> Sheet Two is manually entered. 
> 
> Sheet one is calculated.
> 
> I did confirm there are no blanks.
> 
> I can't help but think I'm soing something wrong.  Any other thoughts?
> 
> Jim
> 
> "Eduardo" wrote:
> 
> > Hi Jim,
> > Is the % in sheet2 calculated or manually entered, or come from a database. 
> > Check if there is not any blank space at the beginning, you can use this 
> > formula to remove any blank space and then copy as value on top
> > 
> > =trim(a1)
> > 
> > 
> > 
> > "Jim" wrote:
> > 
> > > Eduardo,
> > > 
> > > Thanks for the help.  The formula you provided I have tried, it is the one 
> > > returning the error.  Any other suggestions about how to make this vlookup 
> > > work?
> > > 
> > > Your questions, the same percentage will not occur twice.
> > > 
> > > Thanks
> > > Jim
> > > 
> > > 
> > > 
> > > "Eduardo" wrote:
> > > 
> > > > Hi,
> > > > try
> > > > 
> > > > =VLOOKUP(C1,Sheet2!A:B,2,FALSE)
> > > > 
> > > > but I have some questions what if you have the same % more than once, 
> > > > vlookup will bring the first amount it found,
> > > > is it possible to have same % with dif amounts ?
> > > > 
> > > > "Jim" wrote:
> > > > 
> > > > > Hello,
> > > > > 
> > > > > I would like to ask for help.  Am I able to vlookup from a field?  Here is 
> > > > > what I'm doing:
> > > > > 
> > > > > - on sheet two I have a lookup field (Column 1 is the percentage, column 2 
> > > > > is the amount paid if that percentage is reached)
> > > > > 
> > > > > - in Sheet 1 I have a quota listed in A1; the actual amount reached in A2, 
> > > > > and the percent to quota in A3 (A2/A1)
> > > > > 
> > > > > In A4 I would like to have the lookup return the paid out amount based on 
> > > > > the percentage shown in A3, however I am receiving a #N/A.
> > > > > 
> > > > > The only thing I can think of is that I cannot use a vlookup for this type 
> > > > > of calculation.
> > > > > 
> > > > > Any suggestions on how I may be able to complete this task?
> > > > > 
> > > > > Thanks
> > > > > Jim
0
Utf
12/8/2009 3:49:01 PM
Reply:

Similar Artilces:

text field with input mask
Hi all,I have a text field with field size of 6, input mask of &99.99. So it is ok for me to data entry data like E55.66, or 777.88. Now I need enter E123.45. If I change it to field size 7 and input mask of C999.99, I can enter E123.45, but all of my old data will be wrong. What should I do to keep all the old data right and also be able to enter my now data?Thanks,Blinda "Blinda" wrote:> Hi all,> > I have a text field with field size of 6, input mask of &99.99. So it is ok > for me to data entry data like E55.66, or 777.88. > > Now I need enter E123.45. If...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Fields not showing up in columns
I have a field titled PageNumber in a table that is not showing up in my columns, although it is checked in the design view and is not hidden. On the form view I have access to this field and it has all the entries, which would indicate that nothing has been accidentally deleted. What's happened, and how can I fix this so the page number column shows up in the table. The terms you are using and the way you use them are confusing to me. Access has 'fields' in table. Those fields are displayed in columns. When you say 'I have a field titled PageNumber in a t...

in vba for access how do i get the day of the week for a date field?
in vba for access how do i get the day of the week for a date field? You mean Monday, Tuesday,... Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Format(myDate, "dddd") or the day portion of the date? Dim myDate As Date myDate = DateSerial(2005, 12, 25) MsgBox Day(myDate) Daniel wrote: > > in vba for access how do i get the day of the week for a date field? -- Dave Peterson ...

Reference another workbook (variable name) inside a formula cell
I use a formula to reference a another workbook cell. The other workbook name can changes after sent to another party and returned ammended. Rather than change the name of the spreadsheet, I would prefer to change the reference in a cell of the first spreadsheet. The formula below activates the lookup if cell c75 is Y. I would like to replace the text [Midwest G1 5000.xls] with a cell containing the name. =IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0) I have tried using "&" and direct cell references...

Formula Needed!
I need a formula that finds the closest sum of the list of bills to the specified amount, i.e Bills $ 30.00 $ 20.00 $ 30.00 Amount $ 70.00 In this case formula should highlight or indicate in some way cells with amounts $30.00 and $30.00 and show the total of these cells. -- Roman ------------------------------------------------------------------------ Roman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24442 View this thread: http://www.excelforum.com/showthread.php?threadid=380407 Try this, Roman: http://www.mrexcel.com/pc09.shtml ******************...

I need a formula #3
Hi, I need a formula to do this, I think some function may be used A1 : Mar- A2: Mar- How could A3 be: Mar-1/Mar- Thanks Hi LDH! It depends upon whether or not your settings are US or Non-US and whether you are using dates or text. If A1 and A2 are dates and you use non-US date settings: =TEXT(A1,"mmm-yy")&"/"&TEXT(A2,"mmm-yy") Not quite what you asked for: Mar-01/Mar-02 If A1 and A2 are dates and you use US date settings: =TEXT(A1,"mmm-d")&"/"&TEXT(A2,"mmm-d") If A1 and A2 are text: =A1&"/"&A...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Formulas stop calculating
I have an existing spreadsheet with different formulas in it. Just recently I noticed that the forumlas stopped calculating. It's odd since I can cut a formula out of a cell and paste it back into the same cell and it will work again. What is causing this? It's annoying to have to spend 30 mins updating formulas this way when I have only 2 mintues worth of data to imput. Brodie: Go to Tools, Options, Calculate Tab, and check to see if your spreadsheet is set to calculate automatically. If it is not then you will need to set it so that it is by clicking the appropriate radi...

How do I sort worksheet without affecting formula values?
I have two worksheets, one of which has formulas which reference the other. The problem is, I cannot sort the first worksheet and have the formula values in the second worksheet follow it so the values stay the same. I have tried making them absolute references ($A$1) but it doesn't help. I feel your pain. I've ran into the same issue before. The problem is the use of worksheet functions. The solution is DO NOT USE worksheet functions. Use a VBA subroutine instead. Here is an example: With worksheet functions.... A B ...

Retain Field Format From Excel
I have an excel file that I'm importing to Access. This field consists of "time"...hours, minutes, seconds. The format in excel is set to "Custom" - [h]:mm:ss. The data elements look like 150:27:50, 2:30:25, etc... The time stays the same for some records but others change. For example...150:27:50 becomes 6:27:50 AM when imported. If someone can help me figure a way to retain the format for all records I would greatly appreciate it. Thank You, Lisa W. 150 divided by 24 = 6.25 or 6 and 1/4 days which is displayed a 6 AM. You need to use a ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Help with a formula
I have a worksheet that has tabs for each month. I want to track the avg use of a material we use for the last five workingdays... So no problem untill I need to go look at the previous month tab for that last few days of the month, to avg with the first few days of the current month. And I want it to skip the cell if it is empty So if I want to do a running five day avg, and skip the cell if it is empty, and look at another sheet, what would that formual look like. My first question may have been a little confusing. so basically the formula looks like this, =IF(K7=""...

Custumize a Field
Can i customize some field properties like Height or Wide? Can I put only a label (without a field) in a form? I'm working with MS CRM3.0 Thanks You can adjust the width of a field by choosing how many columns it will span in the forms customization screen. You cannot customize the height or put only a label on a form with the standard customization tools. You will need to do this using javascript in the OnLoad. HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "Enrique Bravo" <EnriqueBravo@discussions.microsoft.com> wrote in message news:6E5F414...

AP transaction description field lengthening
I would like to extend the AP transaction description field. I believe it is a 30 char field. I have extender. Will that work? I do not have modifyer. You can't lengthen the field of a column in GP, I would recommend adding a new field for your description and use that with Extender. Search the posts here on why that is a bad idea to stretch a field, there are a number of reasons. Mike "2020" <2020@discussions.microsoft.com> wrote in message news:4D5135DA-92DB-44EF-B8B0-FCB61A9A9061@microsoft.com... >I would like to extend the AP transaction description field. I...

Help needed with Formula
I am hoping what I want to achieve can be done. I am not a speadsheet expert so hope someone can help me. I have a column of times in 24 hour format. I have cells to the right of that covering monday to sunday. I want to put a time in 24 hour format that looks to the time set in the column of times that says, for example. The time in cell B 9 is 12:01, the time of 12:20 has been entered in H 9. This is greater than B 9, convert the difference to minutes and insert into H 9 on a different sheet. If the time is identical or before the time in B 9 insert a 0 in H 9. If it helps I can emai...

putting a quote in a cel as a result of a formula
Hi is it possible to replace the nr 4 with a " in this formula? I just want to put a " in the cel Thanks! =IF(D100<>0;(IF(L100=K100;"4";IF(L100<K100;"m";"k")));"") Do you mean this? =IF(D100<>0;(IF(L100=K100;"""";IF(L100<K100;"m";"k")));"") -- HTH RP (remove nothere from the email address if mailing direct) "kyrbi" <kyrbi@datoeternietoe.com> wrote in message news:Kz%Vd.28618$oB5.3036261@phobos.telenet-ops.be... > Hi is it possible to replace ...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Combine fields containing quotes
I am trying to combine several fields into one. Currently my formula looks something like this =D3&"Some text "&E3&" <br><b>more info: </b>"&F3 This all works just fine, except sometimes I'll need to insert html more advanced than a simple bold or break. How do I escape the quotes in my html so they don't affect the formula? =D3&"Some text "&E3&" <br><b>more info: </b>"&F3&Q3 For now I have just simply added my html in another field and appending it this wa...

Error when exporting smartlist to excel
I get this error when I am exporting a smartlist to excel. This happens randomly and only with some smartlists. How can I troubleshoot this. This is GP 9. I have seen same in AA also.I think this might be because when we export to an excel, an excel object will be created and it didn't get closed when you close excel sheet.Sometimes we need to delete from task manager. This is my guess and I'm not sure about this. "Jack Tundra" wrote: > I get this error when I am exporting a smartlist to excel. This happens > randomly and only with some smartlists. How can I t...

Lookup formula question #2
Hi all, I have a Lookup formula setup to validate what the user enters against a list of stock codes, returning the item costs. Stock codes are A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a stockcode starting with letter C, I get the item cost returned from the last letter B stockcode. How can I get it to return P.O.A. This is what I have at present. =IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019,Fittings!$D$1:$D$5019)),0)*AL4 C4 - Parent stock item description F4 - Stock code AL4 - Quantity Any help - greatly appreciated. -- PeterG ------------...

Shortening the Formula
This formula works: =SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) I tried a lot of different ways, but I couldn't find a way to shorten this part: (E3:E1000=6)+(E3:E1000=6.5) I'm sure there is a way to make that more efficient. Thanks You do not really need the double negation. It is used to transform TRUE/FALSE to 1/0 - Excel does this whenever a math operation is performed on a Boolean value. But you are multiplying so the double neg is redundant. either =SUMPRODUCT(((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) or =SUMPRODUCT(--((E3:E1000=6)+...