| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statements
Hi,
I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
There might be a shorter solution out there but this should work
=IF(OR(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Dat a Base'!$A$1:$Y$413,14,FALSE)) "Julie" wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
I must be doing something wrong - I tried your equation and while it did
succeed in putting blanks where the result was 0, it now does not put blanks where athe results were error messages. Here is exactly the equation I tried: =IF(OR(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)), VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)) Do you see the problem? Or, can you tell me the easier way? -- Julie "akphidelt" wrote: There might be a shorter solution out there but this should work =IF(OR(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Dat a Base'!$A$1:$Y$413,14,FALSE)) "Julie" wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
I was unaware that excel did that because I thought it evaluated the
expression to True, but it just creates an error... try this =IF(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)),"" IF(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0,"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE))) "Julie" wrote: I must be doing something wrong - I tried your equation and while it did succeed in putting blanks where the result was 0, it now does not put blanks where athe results were error messages. Here is exactly the equation I tried: =IF(OR(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)), VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)) Do you see the problem? Or, can you tell me the easier way? -- Julie "akphidelt" wrote: There might be a shorter solution out there but this should work =IF(OR(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Dat a Base'!$A$1:$Y$413,14,FALSE)) "Julie" wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
OK - I added one comma to your equation and it worked - I added it right
after the first "" in the equation. Thanks a bunch! -- Julie "akphidelt" wrote: I was unaware that excel did that because I thought it evaluated the expression to True, but it just creates an error... try this =IF(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)),"" IF(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0,"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE))) "Julie" wrote: I must be doing something wrong - I tried your equation and while it did succeed in putting blanks where the result was 0, it now does not put blanks where athe results were error messages. Here is exactly the equation I tried: =IF(OR(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)), VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)) Do you see the problem? Or, can you tell me the easier way? -- Julie "akphidelt" wrote: There might be a shorter solution out there but this should work =IF(OR(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Dat a Base'!$A$1:$Y$413,14,FALSE)) "Julie" wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
Nice call,
glad it worked "Julie" wrote: OK - I added one comma to your equation and it worked - I added it right after the first "" in the equation. Thanks a bunch! -- Julie "akphidelt" wrote: I was unaware that excel did that because I thought it evaluated the expression to True, but it just creates an error... try this =IF(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)),"" IF(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0,"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE))) "Julie" wrote: I must be doing something wrong - I tried your equation and while it did succeed in putting blanks where the result was 0, it now does not put blanks where athe results were error messages. Here is exactly the equation I tried: =IF(OR(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)), VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)) Do you see the problem? Or, can you tell me the easier way? -- Julie "akphidelt" wrote: There might be a shorter solution out there but this should work =IF(OR(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Dat a Base'!$A$1:$Y$413,14,FALSE)) "Julie" wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if statements
I bet you really only want to hide that 0 only if the "sending" cell's value was
empty. You'd still want to see the 0 if that's what the cell contained. If I'm right: =if(isna(vlookup(...)),"",if(vlookup(...)="","",vl ookup(...)))) or =if(iserror(1/len(vlookup(...)),"",vlookup(...)) Julie wrote: Hi, I have been successfully using the following statement: =IF(ISERROR(VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE)) I want to add another if to this, so that I get: If lookup results in error message, leave blank (first part above) If lookup results in the value 0, leave blank (how do I add this?) Otherwise, put result of lookup (second part above) How do I had the the second IF to this equation so that if the lookup results in the value 0, leave blank? -- Julie -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If, then statements | Excel Discussion (Misc queries) | |||
| If, Then Statements | Excel Worksheet Functions | |||
| IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
| IF Statements | Excel Discussion (Misc queries) | |||
| If statements | Excel Worksheet Functions | |||
Linear Mode
