MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

:-)