look up tables and IF statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

=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
 
Hi Rick!

Would this modification do?

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

Hope this helps!
 
Hi RaymundCG
Thanks for the reply. Unfortunately this aolution did not work. Is there
anything else that I could try.
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
 
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!
 
Your a genius. It works great. Many thanks.

RaymundCG said:
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!
Click to expand...
 
RaymundCG said:
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!
--
Click to expand...

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),"")
 
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
 
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.
 
Back
Top