E Eileen Aug 24, 2004 #1 Is there a quick formular that I can change the #n/a (the result of vlookup) into blank, so I can do the calculations? Thanks!
Is there a quick formular that I can change the #n/a (the result of vlookup) into blank, so I can do the calculations? Thanks!
B BenjieLop Aug 24, 2004 #2 Try this ... =if(iserror(your_vlookup_formula),"",(your_vlookup_formula))
A Andrew Aug 24, 2004 #3 You could use an IF statement combined with ISERROR... ie. say your formula currently looks like this: =VLOOKUP(1,A2:C10,2) Change it to: =IF(ISERROR(VLOOKUP(1,A2:C10,2)),"",VLOOKUP(1,A2:C10,2))
You could use an IF statement combined with ISERROR... ie. say your formula currently looks like this: =VLOOKUP(1,A2:C10,2) Change it to: =IF(ISERROR(VLOOKUP(1,A2:C10,2)),"",VLOOKUP(1,A2:C10,2))
B Biff Aug 24, 2004 #4 An alternative, although error trapping is a good idea: Leave the #N/A and use an ISNUMBER call in your calculations: =SUM(IF(ISNUMBER(A1:A100),A1:A100)) Entered as an array: CTRL,SHIFT,ENTER Biff
An alternative, although error trapping is a good idea: Leave the #N/A and use an ISNUMBER call in your calculations: =SUM(IF(ISNUMBER(A1:A100),A1:A100)) Entered as an array: CTRL,SHIFT,ENTER Biff
A Aladin Akyurek Aug 24, 2004 #5 If efficiency (speed) is of interest to you, try to use the 2-cell approach: Y2: =VLOOKUP(...,0) X2: =IF(ISNA(Y2),"",Y2)
If efficiency (speed) is of interest to you, try to use the 2-cell approach: Y2: =VLOOKUP(...,0) X2: =IF(ISNA(Y2),"",Y2)
