| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Function
I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference
column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Hi,
You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Sorry, that was a typo. I do have it set up as 'CHARGES'!A$1:E$250. I have
used this exact formula on the identically formatted worksheets for 3 months. I just can't see where I am going wrong. Thanks. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
I'm not sure if this helps, but when i open the argument box, it shows that
the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Hi,
With the range correct theres nothing wrong with the formula so if it's returning 0 then it isn't finding a match for L2 in the lookup array. Do a manual check. Copy your value from L2 andpaste it into a cell in 'Charges'. Rember Copy and paste don't type it. manually find a match in column A an enter this formula in a cell =A10=H10 If they really match it will return TRUE, I suspect it will return False and the likely culprits are spaces. Mike "infinite1013" wrote: I'm not sure if this helps, but when i open the argument box, it shows that the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Okay. That helps. The result of this was FALSE. The spacing appears to be the
same, but not sure. Any way to check this? "Mike H" wrote: Hi, With the range correct theres nothing wrong with the formula so if it's returning 0 then it isn't finding a match for L2 in the lookup array. Do a manual check. Copy your value from L2 andpaste it into a cell in 'Charges'. Rember Copy and paste don't type it. manually find a match in column A an enter this formula in a cell =A10=H10 If they really match it will return TRUE, I suspect it will return False and the likely culprits are spaces. Mike "infinite1013" wrote: I'm not sure if this helps, but when i open the argument box, it shows that the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Hi,
Len(a1) tells you how many characters are in the cell which you can compare to how many you think you have. The rogue spaces are 'generally' leading or traling and can be removed using =Trim(a1) and drag down You can then copy this corrected range and Paste special - values on top of the offending range in column A. Mike "infinite1013" wrote: Okay. That helps. The result of this was FALSE. The spacing appears to be the same, but not sure. Any way to check this? "Mike H" wrote: Hi, With the range correct theres nothing wrong with the formula so if it's returning 0 then it isn't finding a match for L2 in the lookup array. Do a manual check. Copy your value from L2 andpaste it into a cell in 'Charges'. Rember Copy and paste don't type it. manually find a match in column A an enter this formula in a cell =A10=H10 If they really match it will return TRUE, I suspect it will return False and the likely culprits are spaces. Mike "infinite1013" wrote: I'm not sure if this helps, but when i open the argument box, it shows that the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Success! Thanks so much!
"Mike H" wrote: Hi, Len(a1) tells you how many characters are in the cell which you can compare to how many you think you have. The rogue spaces are 'generally' leading or traling and can be removed using =Trim(a1) and drag down You can then copy this corrected range and Paste special - values on top of the offending range in column A. Mike "infinite1013" wrote: Okay. That helps. The result of this was FALSE. The spacing appears to be the same, but not sure. Any way to check this? "Mike H" wrote: Hi, With the range correct theres nothing wrong with the formula so if it's returning 0 then it isn't finding a match for L2 in the lookup array. Do a manual check. Copy your value from L2 andpaste it into a cell in 'Charges'. Rember Copy and paste don't type it. manually find a match in column A an enter this formula in a cell =A10=H10 If they really match it will return TRUE, I suspect it will return False and the likely culprits are spaces. Mike "infinite1013" wrote: I'm not sure if this helps, but when i open the argument box, it shows that the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP Function
Your most welcome, glad I could help
"infinite1013" wrote: Success! Thanks so much! "Mike H" wrote: Hi, Len(a1) tells you how many characters are in the cell which you can compare to how many you think you have. The rogue spaces are 'generally' leading or traling and can be removed using =Trim(a1) and drag down You can then copy this corrected range and Paste special - values on top of the offending range in column A. Mike "infinite1013" wrote: Okay. That helps. The result of this was FALSE. The spacing appears to be the same, but not sure. Any way to check this? "Mike H" wrote: Hi, With the range correct theres nothing wrong with the formula so if it's returning 0 then it isn't finding a match for L2 in the lookup array. Do a manual check. Copy your value from L2 andpaste it into a cell in 'Charges'. Rember Copy and paste don't type it. manually find a match in column A an enter this formula in a cell =A10=H10 If they really match it will return TRUE, I suspect it will return False and the likely culprits are spaces. Mike "infinite1013" wrote: I'm not sure if this helps, but when i open the argument box, it shows that the VALUE IF FALSE= #N/A in red. "Mike H" wrote: Hi, You have a single column array in your vlookup 'CHARGES'!A$1:A$250 and are trying to return something from column 5. Should it be this 'CHARGES'!A$1:E$250 Mike "infinite1013" wrote: I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference column A on sheet 1 and return a percentage from column 5 on the sheet 1. In order to avoid the #N/A result, I am using the following formula, but getting 0.00% returned when I know there should be another result. This formula has worked for me before, but can't figure out why it isn't working this time. Please help! The formula looks like this: =IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE )),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Embedded VLOOKUP function within IF function | Excel Worksheet Functions | |||
| HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
| how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
| I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
Linear Mode
