look up tables and IF statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick - 27 May 2006 09:03 GMT

=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

Reply to this Message

RaymundCG - 27 May 2006 09:08 GMT

Hi Rick!

Would this modification do?

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

Hope this helps!

Signature

Thanks and kind regards

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

Reply to this Message

Rick - 27 May 2006 09:20 GMT

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

> Hi Rick!
>
[quoted text clipped - 10 lines]
> > with #N/A
> > Can anyone tell me what formula I should be using please

Reply to this Message

Rick - 27 May 2006 09:25 GMT

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 RaymundCG
> Thanks for the reply. Unfortunately this aolution did not work. Is there
[quoted text clipped - 15 lines]
> > > with #N/A
> > > Can anyone tell me what formula I should be using please

Reply to this Message

RaymundCG - 27 May 2006 09:32 GMT

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!

Signature

Thanks and kind regards

> 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.
[quoted text clipped - 20 lines]
> > > > with #N/A
> > > > Can anyone tell me what formula I should be using please

Reply to this Message

Rick - 27 May 2006 09:38 GMT

Your a genius. It works great. Many thanks.

> Hi Rick!
>
[quoted text clipped - 28 lines]
> > > > > with #N/A
> > > > > Can anyone tell me what formula I should be using please

Reply to this Message

RaymundCG - 27 May 2006 09:41 GMT

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

Signature

Thanks and kind regards

> Your a genius. It works great. Many thanks.
>
[quoted text clipped - 30 lines]
> > > > > > with #N/A
> > > > > > Can anyone tell me what formula I should be using please

Reply to this Message

Leo Heuser - 27 May 2006 12:22 GMT

> Hi Rick!
>
[quoted text clipped - 3 lines]
>
> 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),"")

Signature

Best regards
Leo Heuser

Followup to newsgroup only please.

Reply to this Message

RaymundCG - 27 May 2006 14:11 GMT

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

Signature

Thanks and kind regards

> > Hi Rick!
> >
[quoted text clipped - 21 lines]
>
> =IF(AND(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"")

Reply to this Message

JLatham - 29 May 2006 14:10 GMT

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.

> Hi Leo!
>
[quoted text clipped - 27 lines]
> >
> > =IF(AND(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"")

Reply to this Message