Sorry as Dave points out you need the double quotes...

ActiveCell.Formula =
_"=IF(ISNA(VLOOKUP(B2,'LookupTable'!$A$1:$B$100,1, FALSE)),"""",IF(G20,(G2-J2)*E2/G2,""""))"

or

ActiveCell.Formula =
_"=IF(CountIf('LookupTable'!$A$1:$B$100,B2)=0,"""" ,IF(G20,(G2-J2)*E2/G2,""""))"
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

While I would be more inclined to use a countif to see if your value exists
you can use vlookup. What you want to do is to the the ISNA function to check
the return value something like this...

ActiveCell.Formula =
_"=IF(ISNA(VLOOKUP(B2,'LookupTable'!$A$1:$B$100,1, FALSE)),"",IF(G20,(G2-J2)*E2/G2,""))"

--
HTH...

Jim Thomlinson


"Kanga 85" wrote:

I need to lookup a Table to find values, but somtimes my entry is not present
and the function returns '#NA'. In this case I want a blank in column M,
otherwise some function.

The following code does not run and has an error somewhere in the
ActiveCell.Formula.
Thanks for any help.

Range("M2").Select
ActiveCell.Formula =
_"=IF((VLOOKUP(B2,'LookupTable'!$A$1:$B$100,1,FALS E)="#NA"),"",IF(G20,(G2-J2)*E2/G2,""))"
With ActiveSheet
.Range("M2").AutoFill Destination:=Range("M" & lastrow),
Type:=xlFillDefault
End With