vlookup if not in Table

[Guest]

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,FALSE)="#NA"),"",IF(G2>0,(G2-J2)*E2/G2,""))"
With ActiveSheet
.Range("M2").AutoFill Destination:=Range("M" & lastrow),
Type:=xlFillDefault
End With

 

[Dave Peterson]

Try doubling up on the double quotes and using =if(iserror(

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

But since you're only checking column A, you could use =if(iserror(match(...)))

ActiveCell.Formula = _
"=IF(iserror(match(B2,'LookupTable'!$A$1:$a$100,0)),""""," _
& "IF(G2>0,(G2-J2)*E2/G2,""""))"

 

[Guest]

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(G2>0,(G2-J2)*E2/G2,""))"

 

[Guest]

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

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

or

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

 

[Dave Peterson]

Watch that =countif() version. You'll only want to look at A1:A100.

 

[Guest]

Thanks Dave and Jim

Try doubling up on the double quotes and using =if(iserror(

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

But since you're only checking column A, you could use =if(iserror(match(...)))

ActiveCell.Formula = _
"=IF(iserror(match(B2,'LookupTable'!$A$1:$a$100,0)),""""," _
& "IF(G2>0,(G2-J2)*E2/G2,""""))"