| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and IF AND problem
I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to
calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
I suspect that your VLOOKUP formula in G2 is actually returning " "
(i.e. a space) rather than "" when it returns a blank - check this out and correct it. Your first IF formula should then work. Hope this helps. Pete excelnewbie44 wrote: I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
You might try this:
=IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Sorry, I made a mistake in the formula for G2" ". The correct formula
should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Ok.. One more time. I'll get it right.
=IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Maybe you could just check to see how many numbers you have:
=if(count(f2:g2)<2,"",f2-g2) or if you want to treate those "" as 0's: =n(f2)-n(g2) excelnewbie44 wrote: I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Here is the VLOOKUP function I used:
=IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A15 8,TABLE,3,FALSE)) I tried your suggestions and it still gives me the same error. Any other suggestions? "PCLIVE" wrote: Ok.. One more time. I'll get it right. =IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Ok.
I was assuming you were using a space in G2 if nothing was found. I've removed the space in G2=" ". That may work for you. It seems to work or my end. =IF(OR(F2=0,G2=0,G2=""),"",F2-G2) "excelnewbie44" wrote in message ... Here is the VLOOKUP function I used: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A15 8,TABLE,3,FALSE)) I tried your suggestions and it still gives me the same error. Any other suggestions? "PCLIVE" wrote: Ok.. One more time. I'll get it right. =IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
I don't understand why you are checking if A2 exists in your TABLE, and
if it does you then try to match using A158 - if this doesn't exist then it will return an error. Shouldn't your formula be: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A2, TABLE,3,FALSE)) or =IF(ISNA(VLOOKUP(A158,TABLE,3,FALSE)),"",VLOOKUP(A 158,TABLE,3,FALSE)) Hope this helps. Pete excelnewbie44 wrote: Here is the VLOOKUP function I used: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A15 8,TABLE,3,FALSE)) I tried your suggestions and it still gives me the same error. Any other suggestions? "PCLIVE" wrote: Ok.. One more time. I'll get it right. =IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
I apologize, here is the forumla again, I sent it too early before I could
change it completely around to correspond to my original question about cells F2 and G2 =IF(ISNA(VLOOKUP(A2,table,3,FALSE)),"",VLOOKUP(A2, table,3,FALSE)) "Pete_UK" wrote: I don't understand why you are checking if A2 exists in your TABLE, and if it does you then try to match using A158 - if this doesn't exist then it will return an error. Shouldn't your formula be: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A2, TABLE,3,FALSE)) or =IF(ISNA(VLOOKUP(A158,TABLE,3,FALSE)),"",VLOOKUP(A 158,TABLE,3,FALSE)) Hope this helps. Pete excelnewbie44 wrote: Here is the VLOOKUP function I used: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A15 8,TABLE,3,FALSE)) I tried your suggestions and it still gives me the same error. Any other suggestions? "PCLIVE" wrote: Ok.. One more time. I'll get it right. =IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP and IF AND problem
Thank you very much, that fixed it!
"PCLIVE" wrote: Ok. I was assuming you were using a space in G2 if nothing was found. I've removed the space in G2=" ". That may work for you. It seems to work or my end. =IF(OR(F2=0,G2=0,G2=""),"",F2-G2) "excelnewbie44" wrote in message ... Here is the VLOOKUP function I used: =IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A15 8,TABLE,3,FALSE)) I tried your suggestions and it still gives me the same error. Any other suggestions? "PCLIVE" wrote: Ok.. One more time. I'll get it right. =IF(OR(F2=0,G2=0,G2=" "),"",F2-G2) "PCLIVE" wrote in message ... Sorry, I made a mistake in the formula for G2" ". The correct formula should be: =IF(OR(F20,G20,G2=" "),F2-G2,"") "PCLIVE" wrote in message ... You might try this: =IF(OR(F20,G20,G2" "),F2-G2,"") Regards, Paul "excelnewbie44" wrote in message ... I am trying to using the following function: =IF(AND(F20,G20),F2-G2,"") to calculate the difference between F2 & G2 if there is a value in both, if not I want it to return a blank cell. In the cell G2 I have a Vlookup function that looks up values in a table, if there is not value for what it is looking up I have it returning a blank answer. When I do this and it returns a blank answer in G2, my answer to my equation gives me a #VALUE!. I have also tried the following fuction: =IF(ISBLANK(F2),"",IF(ISBLANK(G2),"",F2-G2)) and this returns the same answer. Any idea of how I can fix this problem? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| Vlookup problem.. | Excel Worksheet Functions | |||
| vlookup problem, possibly due to "noise" | Excel Worksheet Functions | |||
| VLOOKUP Problem (limitation)? | Excel Worksheet Functions | |||
| VLookup problem | Excel Worksheet Functions | |||
Linear Mode
