|
vlookup if not in Table
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 |
vlookup if not in Table
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(G20,(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(G20,(G2-J2)*E2/G2,""""))" 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 -- Dave Peterson |
vlookup if not in Table
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 |
vlookup if not in Table
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 |
vlookup if not in Table
Watch that =countif() version. You'll only want to look at A1:A100.
Jim Thomlinson wrote: 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 -- Dave Peterson |
vlookup if not in Table
Thanks Dave and Jim
"Dave Peterson" wrote: 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(G20,(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(G20,(G2-J2)*E2/G2,""""))" 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 -- Dave Peterson |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com