Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
When using:
=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A. This is causing me fits because 0 is a valid value and I therefore cannot sort out the 'not founds'. I have verified that the cells are in fact empty, and do not contain zeros. Why would excel be returning a 0 instead of #N/A? Any help would be greatly appreciated. Thanks, Robert |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
=VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an
exact match is not found, Excel is returning a 0 instead of the #N/A That's not possible. If the formula returns 0 it's finding an exact match but if column 5 contains an empty cell then the result will be 0. If you want the #N/A when column 5 is empty: =IF(VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)="",#N/A,VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)) -- Biff Microsoft Excel MVP "RobertSD" wrote in message ... When using: =VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A. This is causing me fits because 0 is a valid value and I therefore cannot sort out the 'not founds'. I have verified that the cells are in fact empty, and do not contain zeros. Why would excel be returning a 0 instead of #N/A? Any help would be greatly appreciated. Thanks, Robert |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
Hi,
Excel will return zero if it finds an exact match in colum 1 of the lookup array and there is a zero or blank cell in column 5 of the lookup array. Check you data again and I'm sure you'll find there is a match for A5. Mike "RobertSD" wrote: When using: =VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A. This is causing me fits because 0 is a valid value and I therefore cannot sort out the 'not founds'. I have verified that the cells are in fact empty, and do not contain zeros. Why would excel be returning a 0 instead of #N/A? Any help would be greatly appreciated. Thanks, Robert |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
Thanks for the reply, but this is not how VLOOKUP has been functioning for me
for the past 5 1/2 years (and we have been on 2007 for the past year). Until now, every time excel found an exact match and the column I requested contained an empty cell, the formula would return #N/A. This worked perfectly for me because many of the cells contain data (including the value 0) while many others are empty. Now, are you telling me that when an exact match is found and the column I'm requesting has a zero in it, excel will return a zero, and when the column I'm requesting is empty, excel will return a zero? An empty cell is not the same as a cell with the value 0 in it (a cell with the value zero is not empty). How is that helpful if the formula can't differentiate between an empty cell and zero? As it stands today, the VLOOKUP function is no longer useful to me as a tool. I guarantee that I have NEVER had to use any kind of IF statements with my VLOOKUPs, and I use VLOOKUP a lot. One important note: I just received a new laptop last week. What excel setting or configuration could be different that would cause the different functionality? Thanks, Robert "T. Valko" wrote: =VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A That's not possible. If the formula returns 0 it's finding an exact match but if column 5 contains an empty cell then the result will be 0. If you want the #N/A when column 5 is empty: =IF(VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)="",#N/A,VLOOKUP(A5,'010425'!$A$5:$F$3368,5,0)) -- Biff Microsoft Excel MVP "RobertSD" wrote in message ... When using: =VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A. This is causing me fits because 0 is a valid value and I therefore cannot sort out the 'not founds'. I have verified that the cells are in fact empty, and do not contain zeros. Why would excel be returning a 0 instead of #N/A? Any help would be greatly appreciated. Thanks, Robert |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
"RobertSD" wrote in message ... Thanks for the reply, but this is not how VLOOKUP has been functioning for me for the past 5 1/2 years (and we have been on 2007 for the past year). Until now, every time excel found an exact match and the column I requested contained an empty cell, the formula would return #N/A. This worked perfectly for me because many of the cells contain data (including the value 0) while many others are empty. Now, are you telling me that when an exact match is found and the column I'm requesting has a zero in it, excel will return a zero, and when the column I'm requesting is empty, excel will return a zero? An empty cell is not the same as a cell with the value 0 in it (a cell with the value zero is not empty). How is that helpful if the formula can't differentiate between an empty cell and zero? As it stands today, the VLOOKUP function is no longer useful to me as a tool. I guarantee that I have NEVER had to use any kind of IF statements with my VLOOKUPs, and I use VLOOKUP a lot. One important note: I just received a new laptop last week. What excel setting or configuration could be different that would cause the different functionality? Thanks, Robert Not possible, the only way you will get #N/A is when the lookup value does not match in the 1st column , if the cell that is returned from the 5th column is blank you will get zero and that goes for all version that has VLOOKUP as a function. -- Regards, Peo Sjoblom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
I appreciate people trying to offer help, but essentially calling me a liar
isn't productive. What I'm describing IS POSSIBLE, and is exactly how it has functioned for me for over 5 years - UNTIL I got this new laptop. I am not smoking crack, I am not new to this, and I have used the described funtionality a bazillion times (and could recite it back in my sleep). Please don't try to make me believe I don't know what I'm talking about. Is there anyone who can think outside the box enough to believe what I'm saying is true, and then hopefully offer something helpful? "Peo Sjoblom" wrote: "RobertSD" wrote in message ... Thanks for the reply, but this is not how VLOOKUP has been functioning for me for the past 5 1/2 years (and we have been on 2007 for the past year). Until now, every time excel found an exact match and the column I requested contained an empty cell, the formula would return #N/A. This worked perfectly for me because many of the cells contain data (including the value 0) while many others are empty. Now, are you telling me that when an exact match is found and the column I'm requesting has a zero in it, excel will return a zero, and when the column I'm requesting is empty, excel will return a zero? An empty cell is not the same as a cell with the value 0 in it (a cell with the value zero is not empty). How is that helpful if the formula can't differentiate between an empty cell and zero? As it stands today, the VLOOKUP function is no longer useful to me as a tool. I guarantee that I have NEVER had to use any kind of IF statements with my VLOOKUPs, and I use VLOOKUP a lot. One important note: I just received a new laptop last week. What excel setting or configuration could be different that would cause the different functionality? Thanks, Robert Not possible, the only way you will get #N/A is when the lookup value does not match in the 1st column , if the cell that is returned from the 5th column is blank you will get zero and that goes for all version that has VLOOKUP as a function. -- Regards, Peo Sjoblom |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
No need to get defensive!
If you've used VLOOKUP a bazillion times I can say with certainty that I've used it a bazillion * bazillion and I know how it works. You may be getting the results you say but it is *not possible* for VLOOKUP to work the way you're describing. I suspect you have data that doesn't quite "match" as you think it should or should not. For example, you want to lookup the value in cell A1 which shows as 100. You have a 100 in your table but you get #N/A as the result. One of the 100's is not exactly 100. One (or both) may be the result of a formula and the displayed value is not the true underlying value. The true underlying value of cell A1 may be 100.000002456 but it *displays* as 100 so you think the value is 100 and 100 should matche 100 in the table. Other common causes are unseen non-printing characters that may be present like leading/trailing spaces or data type mismatches where you may have TEXT numbers being compared to NUMERIC numbers. How is that helpful if the formula can't differentiate between an empty cell and zero? It's up to you to write the formula to take that into consideration if need be. An empty cell evaluates as 0 depending on what type of function/formula you're using. Don't believe us? Make sure cell A1 is empty then enter this formula in B1: =A1. What result did you get? You'd be surprised at how many times Excel "humbled" experienced users because the user thought they knew what they were doing! Myself included! You have to start from the position that *Excel is always right* and find your mistake. That mistake is often not understanding how Excel really works. -- Biff Microsoft Excel MVP "RobertSD" wrote in message ... I appreciate people trying to offer help, but essentially calling me a liar isn't productive. What I'm describing IS POSSIBLE, and is exactly how it has functioned for me for over 5 years - UNTIL I got this new laptop. I am not smoking crack, I am not new to this, and I have used the described funtionality a bazillion times (and could recite it back in my sleep). Please don't try to make me believe I don't know what I'm talking about. Is there anyone who can think outside the box enough to believe what I'm saying is true, and then hopefully offer something helpful? "Peo Sjoblom" wrote: "RobertSD" wrote in message ... Thanks for the reply, but this is not how VLOOKUP has been functioning for me for the past 5 1/2 years (and we have been on 2007 for the past year). Until now, every time excel found an exact match and the column I requested contained an empty cell, the formula would return #N/A. This worked perfectly for me because many of the cells contain data (including the value 0) while many others are empty. Now, are you telling me that when an exact match is found and the column I'm requesting has a zero in it, excel will return a zero, and when the column I'm requesting is empty, excel will return a zero? An empty cell is not the same as a cell with the value 0 in it (a cell with the value zero is not empty). How is that helpful if the formula can't differentiate between an empty cell and zero? As it stands today, the VLOOKUP function is no longer useful to me as a tool. I guarantee that I have NEVER had to use any kind of IF statements with my VLOOKUPs, and I use VLOOKUP a lot. One important note: I just received a new laptop last week. What excel setting or configuration could be different that would cause the different functionality? Thanks, Robert Not possible, the only way you will get #N/A is when the lookup value does not match in the 1st column , if the cell that is returned from the 5th column is blank you will get zero and that goes for all version that has VLOOKUP as a function. -- Regards, Peo Sjoblom |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
I didn't call you a liar, but if you insist that VLOOKUP works that way you
are either seriously delusional or a straight face liar. -- Regards, Peo Sjoblom "RobertSD" wrote in message ... I appreciate people trying to offer help, but essentially calling me a liar isn't productive. What I'm describing IS POSSIBLE, and is exactly how it has functioned for me for over 5 years - UNTIL I got this new laptop. I am not smoking crack, I am not new to this, and I have used the described funtionality a bazillion times (and could recite it back in my sleep). Please don't try to make me believe I don't know what I'm talking about. Is there anyone who can think outside the box enough to believe what I'm saying is true, and then hopefully offer something helpful? "Peo Sjoblom" wrote: |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 0 instead of #N/A when no value is found
If A5 is empty, then try toggling this setting:
(in xl2003 menus) Tools|Options|Transition tab|Uncheck Transition Formula Evaluation (I'd uncheck all those transition settings) If you're using xl2007, I think it's buried under: Office Button|Excel Options Then I have no idea! RobertSD wrote: When using: =VLOOKUP(A5,'010425'!$A$5:$F$3368,5,FALSE) and an exact match is not found, Excel is returning a 0 instead of the #N/A. This is causing me fits because 0 is a valid value and I therefore cannot sort out the 'not founds'. I have verified that the cells are in fact empty, and do not contain zeros. Why would excel be returning a 0 instead of #N/A? Any help would be greatly appreciated. Thanks, Robert -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an alternative value if lookup cannot be found in array | Excel Discussion (Misc queries) | |||
Not Found Function | Excel Discussion (Misc queries) | |||
VLookup Value not found ? | Excel Discussion (Misc queries) | |||
vlookup not found | Excel Worksheet Functions | |||
IF NOT FOUND | Excel Worksheet Functions |