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

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

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

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.

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

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

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.