Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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*pm, 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from a formula field
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup last character of 102 char field | Excel Worksheet Functions | |||
vlookup and a function field input | Excel Worksheet Functions | |||
create formula. 1 field constant and another field varies by inpu. | Setting up and Configuration of Excel | |||
Vlookup not recognizing field | Excel Worksheet Functions | |||
VLookup accesses half the text in a field? | Excel Worksheet Functions |