| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup Table - with L/U value as '100 | Excel Discussion (Misc queries) | |||
| Vlookup Table Help | New Users to Excel | |||
| Vlookup table | Excel Discussion (Misc queries) | |||
| Table with VLOOKUP? | Excel Discussion (Misc queries) | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
