VLOOKUP and IF AND problem

[Guest]

I am trying to using the following function: =IF(AND(F2>0,G2>0),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?

 

[Pete_UK]

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

 

[PCLIVE]

You might try this:

=IF(OR(F2>0,G2>0,G2>" "),F2-G2,"")

Regards,
Paul

 

[PCLIVE]

Sorry, I made a mistake in the formula for G2>" ". The correct formula
should be:
=IF(OR(F2>0,G2>0,G2=" "),F2-G2,"")

 

[PCLIVE]

Ok.. One more time. I'll get it right.

=IF(OR(F2=0,G2=0,G2=" "),"",F2-G2)

 

[Dave Peterson]

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)

 

[Guest]

Here is the VLOOKUP function I used:
=IF(ISNA(VLOOKUP(A2,TABLE,3,FALSE)),"",VLOOKUP(A158,TABLE,3,FALSE))
I tried your suggestions and it still gives me the same error. Any other
suggestions?

 

[PCLIVE]

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)

 

[Pete_UK]

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(A158,TABLE,3,FALSE))

Hope this helps.

Pete

 

[Guest]

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

 

[Guest]

Thank you very much, that fixed it!

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)