| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refreshing Pivot Tables linked to Oracle Datasource | Excel Discussion (Misc queries) | |||
| How do I keep file sizes small when using multiple pivot tables? | Excel Discussion (Misc queries) | |||
| How to pull numbers from two tables with conditions? | Excel Worksheet Functions | |||
| Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
| How to paste Xcel tables legibly into Word . Help!! | Excel Discussion (Misc queries) | |||
Linear Mode
