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

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

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

=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

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.