VLOOKUP
Posted by lucky on February 04, 2002 5:26 AM
If a VLOOKUP returns '#N/A' can I return another result like'0' instead???????
Cheers!
Posted by Chris D on February 04, 2002 5:55 AM
yep :
=if(isna(original formula)=true,0,original formula)
should return 0 if there is an #N/A about to happen
HTH
Chris
Posted by Aladin Akyurek on February 04, 2002 6:00 AM
For exact match, use:
=IF(COUNTIF(E2:E40),VLOOKUP(A2,E2:G40,2,0),0)
For approximate match, use:
=IF(ISNUMBER(MATCH(A2,E2:E40)),VLOOKUP(A2,E2:G40,2),0)
where A2 is the lookup value and E2:G40 the lookup table. The 0 as the last argument of VLOOKUP is the same as the logical FALSE.
=============
Posted by lucky on February 04, 2002 6:38 AM
Cheers Fellas
Posted by Angela on February 04, 2002 8:21 AM
Or you could use the following formula
=if(iserror(vlookup formula),0,(vlookup formula))
Posted by Chris D on February 04, 2002 11:31 AM
Hi Angela,
be mindful when using =iserror just to trap an #N/A "error"
see 14328.htmlthese previous postings about the =iserror function
:-)
