look up tables and IF statements
 

=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

look up tables and IF statements
 

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

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

"RaymundCG" wrote:

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

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

"Rick" wrote:

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

"RaymundCG" wrote:

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

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!
--
Thanks and kind regards


"Rick" wrote:

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

"Rick" wrote:

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

"RaymundCG" wrote:

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

Your a genius. It works great. Many thanks.

"RaymundCG" wrote:

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!
--
Thanks and kind regards


"Rick" wrote:

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

"Rick" wrote:

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

"RaymundCG" wrote:

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

You're much welcome Rick; thanks also for posting back!
--
Thanks and kind regards


"Rick" wrote:

Your a genius. It works great. Many thanks.

"RaymundCG" wrote:

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!
--
Thanks and kind regards


"Rick" wrote:

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

"Rick" wrote:

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

"RaymundCG" wrote:

Hi Rick!

Would this modification do?

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

Hope this helps!
--
Thanks and kind regards


"Rick" wrote:

=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

look up tables and IF statements
 

"RaymundCG" skrev i en meddelelse
...
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)," ")

--
Best regards
Leo Heuser

Followup to newsgroup only please.

look up tables and IF statements
 

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

--
Thanks and kind regards


"Leo Heuser" wrote:

"RaymundCG" skrev i en meddelelse
...
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)," ")

--
Best regards
Leo Heuser

Followup to newsgroup only please.

look up tables and IF statements
 

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.

"RaymundCG" wrote:

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

--
Thanks and kind regards


"Leo Heuser" wrote:

"RaymundCG" skrev i en meddelelse
...
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)," ")

--
Best regards
Leo Heuser

Followup to newsgroup only please.