look up tables and IF statements

[Rick]

=VLOOKUP(G2,$C$30:$F$369,2)
I have managed to create a look up table using the office assistant, and the
formula above dumps information into cell G3. If G2 is empty or has a zero I
don't want G3 to have anything in it. At the moment it just keeps coming up
with #N/A
Can anyone tell me what formula I should be using please

 

[RaymundCG]

Hi Rick!

Would this modification do?

=IF(OR(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"")

Hope this helps!

 

[Rick]

Hi RaymundCG
Thanks for the reply. Unfortunately this aolution did not work. Is there
anything else that I could try.
Rick

 

[Rick]

Apologies: It works if there is nothing in the box, but not if there is a
zero in the box, and I can work with it like that, many thanks.

Rick

 

[RaymundCG]

Hi Rick!

I re-tested the formula and it seems that this one may be better...

=IF(OR(G2=0,G2=""),"",VLOOKUP(G2,$C$30:$F$369,2))

Hope this helps!

 

[Rick]

Your a genius. It works great. Many thanks.

Hi Rick!

I re-tested the formula and it seems that this one may be better...

=IF(OR(G2=0,G2=""),"",VLOOKUP(G2,$C$30:$F$369,2))

Hope this helps!

 

[RaymundCG]

You're much welcome Rick; thanks also for posting back!

 

[Leo Heuser]

Hi Rick!

I re-tested the formula and it seems that this one may be better...

=IF(OR(G2=0,G2=""),"",VLOOKUP(G2,$C$30:$F$369,2))

Hope this helps!
--

Hi Raymund

This in an example of De Morgan's Laws, which has to do with
negating propositions.

If you negate the compound proposition
G2=0 OR G2=""

De Morgan states, that the propositions are negated and OR
is turned to AND (and vice versa), so

neg(G2=0 OR G2="") will be
G2<>0 AND G2<>""

Or in your first answer:

=IF(AND(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"")

 

[RaymundCG]

Hi Leo!

Thanks for the info! I'm not that familiar with De Morgan's Laws, I will
look into that also. Maybe that explains why sometimes I do get strange
results with my calculations. : P

 

[JLatham]

RaymundCG, another way to deal with it is to look for the specific error
generated when the VLOOKUP fails, as
=IF(ISNA(VLOOKUP(G2,$C$30:$F$369,2)),"",VLOOKUP(G2,$C$30:$F$369,2))
there are other "IS" functions that can be used in similar situations:
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

ISERR and ISERROR are often useful in situations like this one.