Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtpy?
This formula is in M3:
=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) And it returns a "0" in that cell when the VLOOKUP source cell is empty. Sometimes, however, but not always, there will be no value there but the "0" is a problem. Because of that zero, the formula in the adjacent cell L3 is no longer calculating correctly. I didn't have the L3 formula narrowed down to a specific character so that it would work, the formula just would return a complete rather than discounted value when the box in M3 had an "X" in it. But I decided it would be smarter to put the "X" in the VLOOKUP source sheet rather than adding them manually later as I then don't have to look it up each and every time for any item ordered. But this has thrown the entire sheet off as that zero value result means that I get all "no discount" values returned now, no matter what is the actual case. Is there a way to fix that formula in M3 above, the =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) one, so that if the corresponding cell in column 7 is empty that it makes M3 remain completely and entirely empty anyway without adding that "0"? Thank you! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtpy?
StargateFanNotAtHome,
=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF( VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3 ,GrandToy2008,7,FALSE)) HTH, Bernie MS Excel MVP "StargateFanNotAtHome" wrote in message ... This formula is in M3: =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) And it returns a "0" in that cell when the VLOOKUP source cell is empty. Sometimes, however, but not always, there will be no value there but the "0" is a problem. Because of that zero, the formula in the adjacent cell L3 is no longer calculating correctly. I didn't have the L3 formula narrowed down to a specific character so that it would work, the formula just would return a complete rather than discounted value when the box in M3 had an "X" in it. But I decided it would be smarter to put the "X" in the VLOOKUP source sheet rather than adding them manually later as I then don't have to look it up each and every time for any item ordered. But this has thrown the entire sheet off as that zero value result means that I get all "no discount" values returned now, no matter what is the actual case. Is there a way to fix that formula in M3 above, the =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) one, so that if the corresponding cell in column 7 is empty that it makes M3 remain completely and entirely empty anyway without adding that "0"? Thank you! :oD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtp
Bernie, what happens if it's supposed to return zero? ;)
Barb Reinhardt "Bernie Deitrick" wrote: StargateFanNotAtHome, =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF( VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3 ,GrandToy2008,7,FALSE)) HTH, Bernie MS Excel MVP "StargateFanNotAtHome" wrote in message ... This formula is in M3: =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) And it returns a "0" in that cell when the VLOOKUP source cell is empty. Sometimes, however, but not always, there will be no value there but the "0" is a problem. Because of that zero, the formula in the adjacent cell L3 is no longer calculating correctly. I didn't have the L3 formula narrowed down to a specific character so that it would work, the formula just would return a complete rather than discounted value when the box in M3 had an "X" in it. But I decided it would be smarter to put the "X" in the VLOOKUP source sheet rather than adding them manually later as I then don't have to look it up each and every time for any item ordered. But this has thrown the entire sheet off as that zero value result means that I get all "no discount" values returned now, no matter what is the actual case. Is there a way to fix that formula in M3 above, the =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) one, so that if the corresponding cell in column 7 is empty that it makes M3 remain completely and entirely empty anyway without adding that "0"? Thank you! :oD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtpy?
StargateFanNotAtHome wrote:
This formula is in M3: =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) And it returns a "0" in that cell when the VLOOKUP source cell is empty. Sometimes, however, but not always, there will be no value there but the "0" is a problem. Because of that zero, the formula in the adjacent cell L3 is no longer calculating correctly. I didn't have the L3 formula narrowed down to a specific character so that it would work, the formula just would return a complete rather than discounted value when the box in M3 had an "X" in it. But I decided it would be smarter to put the "X" in the VLOOKUP source sheet rather than adding them manually later as I then don't have to look it up each and every time for any item ordered. But this has thrown the entire sheet off as that zero value result means that I get all "no discount" values returned now, no matter what is the actual case. Is there a way to fix that formula in M3 above, the =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) one, so that if the corresponding cell in column 7 is empty that it makes M3 remain completely and entirely empty anyway without adding that "0"? Thank you! :oD Do I get you correctly, your source data sometimes has zero and sometimes has "X"? If so, this overloading of the value field is the root of your problem because VLOOKUP will return zero in either case. Yet, you seem to say the source values mean two different things. So, you would be better off differentiating the values in the source: if zero means one thing (no discount) and "X" means something else (not sure what it means), put them in different columns in the source. It might be possible to write formulas with exception controls but in the end it will likely be easier to separate the metrics up front. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtp
=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",
IF(VLOOKUP($D3,GrandToy2008,7,FALSE)="","", VLOOKUP($D3,GrandToy2008,7,FALSE)) or =if(iserror(1/len(vlookup(...)),"",vlookup(...)) Barb Reinhardt wrote: Bernie, what happens if it's supposed to return zero? ;) Barb Reinhardt "Bernie Deitrick" wrote: StargateFanNotAtHome, =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF( VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3 ,GrandToy2008,7,FALSE)) HTH, Bernie MS Excel MVP "StargateFanNotAtHome" wrote in message ... This formula is in M3: =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) And it returns a "0" in that cell when the VLOOKUP source cell is empty. Sometimes, however, but not always, there will be no value there but the "0" is a problem. Because of that zero, the formula in the adjacent cell L3 is no longer calculating correctly. I didn't have the L3 formula narrowed down to a specific character so that it would work, the formula just would return a complete rather than discounted value when the box in M3 had an "X" in it. But I decided it would be smarter to put the "X" in the VLOOKUP source sheet rather than adding them manually later as I then don't have to look it up each and every time for any item ordered. But this has thrown the entire sheet off as that zero value result means that I get all "no discount" values returned now, no matter what is the actual case. Is there a way to fix that formula in M3 above, the =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLO OKUP($D3,GrandToy2008,7,FALSE)) one, so that if the corresponding cell in column 7 is empty that it makes M3 remain completely and entirely empty anyway without adding that "0"? Thank you! :oD -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtpy?
On Tue, 5 Aug 2008 12:30:27 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote: =IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF( VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3 ,GrandToy2008,7,FALSE))) You did make the correct change - my parentheses checking isn't as good as Excel's obviously ;-) Oh, good! Everything seems to be working, so that was a good sign. <lol Well, yours is better than mine since mine is non-existent <g. Glad Excel fixed, though, for sure! It's working like a dream. When I place my orders at the end of the week, it's going to be a heck of a lot easier! Thanks. :oD HTH, Bernie MS Excel MVP |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP - when source empty, get "0". Change to complete emtpy?
StargateFanNotAtHome wrote:
On Fri, 01 Aug 2008 20:32:12 -0400, smartin wrote: Do I get you correctly, your source data sometimes has zero and sometimes has "X"? No. The source data will either have an X or will be blank. It's the calculation on the vlookup that is on the other sheet that is creating the zero when the source field is empty. Sorry I wasn't clear. If so, this overloading of the value field is the root of your problem because VLOOKUP will return zero in either case. Yet, you seem to say the source values mean two different things. So, you would be better off differentiating the values in the source: if zero means one thing (no discount) and "X" means something else (not sure what it means), put them in different columns in the source. It might be possible to write formulas with exception controls but in the end it will likely be easier to separate the metrics up front. It's just Excel returning a zero to represent the blank in the resulting VLOOKUP calculation, I'm guessing (?). That is correct. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Empty a cell if the values equal to "IN" , "MC" or "PP" | Excel Programming | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
How to change Import Data source folder "permanently"? | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
VLOOKUP gives duplicate results for "empty" searches | Excel Worksheet Functions |