VLOOKUP - when source empty, get "0". Change to complete emtpy?

[StargateFanNotAtHome]

This formula is in M3:
=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLOOKUP($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)),"",VLOOKUP($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! D

 

[Bernie Deitrick]

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

 

[Barb Reinhardt]

Bernie, what happens if it's supposed to return zero?

Barb Reinhardt

 

[smartin]

This formula is in M3:
=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",VLOOKUP($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)),"",VLOOKUP($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! D

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.

 

[Dave Peterson]

=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(...))

 

[StargateFanNotAtHome]

Bernie, what happens if it's supposed to return zero?

Hi, thought I'd address this one. It should never be zero <g>.

There is an X in that field if the source data requires it but
otherwise it is purposefully blank. hth. I wasn't clear, I think.

The zero is throwing off the calculation in L3, which is defeating the
entire purpose of having the ability to know when a product is
discounted or not <g>.

Thanks! D

 

[StargateFanNotAtHome]

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 (?).

 

[StargateFanNotAtHome]

StargateFanNotAtHome,

=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF(VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3,GrandToy2008,7,FALSE))

You guys are ABSOLUTELY THE BEST!! You've saved my bacon yet again!

I think that in my career, this is the 2nd spreadsheet that has gotten
rid of the biggest headaches for me in what I do. All the
spreadsheets are super important, but this one will now start saving
me hours of work each week since the previous process they had here
wasn't efficient enough.

When I go to place orders now, I populate the source sheet with the
information which now includes an "X" in the new field relating to
whether or not it's discounted. This new feature for me, VLOOKUP,
then dumps the info all across the line with my just entering the
product ID code. And now, with this solution, the X stops the
discount calculation from happening and so and shows the real price.
I'm placing one of my weekly orders today. The spreadsheet should now
reflect the printout from the onlin order sheet!

Thank you to everyone! I know I don't say that enough. Your service
here in these ngs is extremely valuable! <g>

Thanks, Bernie. There was a tiny error, I don't know exactly what but
XL2003 advised me to accept the change and I said, what the heck, and
then it worked. All I could see was an extra ")". But here is the
formula after 2003 did something to it:

=IF(ISNA(VLOOKUP($D3,GrandToy2008,7,FALSE)),"",IF(VLOOKUP($D3,GrandToy2008,7,FALSE)=0,"",VLOOKUP($D3,GrandToy2008,7,FALSE)))

Thanks and cheers. D

 

[Bernie Deitrick]

=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 ;-)

HTH,
Bernie
MS Excel MVP

 

[StargateFanNotAtHome]

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. D

 

[smartin]

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.


It's just Excel returning a zero to represent the blank in the
resulting VLOOKUP calculation, I'm guessing (?).

That is correct.