Returning result as 0 from #N/A

[JP1912]

I have created a table, using vlookup, allowing me to copy in phone stats
into an excel spreadsheet, the vlookup formula uses ID's to gather the info,
in situations where the report is missing the ID the cell returns #N/A (no
info error).

Is there any formula that will turn the #N/A into a 0 or blank cell..? I
don't want to macro the sheets for this purpose if possible....

any/ all suggestions welcome..

thanks

 

[Mike H]

try

=IF(ISNA(your_Lookup),"",your_Lookup)

Mike

 

[Sam Wilson]

Rather than use =VLOOKUP(...)

use =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

 

[paul.robinson]

I have created a table, using vlookup, allowing me to copy in phone stats
into an excel spreadsheet, the vlookup formula uses ID's to gather the info,
in situations where the report is missing the ID the cell returns #N/A (no
info error).

Is there any formula that will turn the #N/A into a 0 or blank cell..?  I
don't want to macro the sheets for this purpose if possible....

any/ all suggestions welcome..

thanks

 

[paul.robinson]

Hi
=IF(ISNA(Vlookup(.....),0,Vlookup(.....))

where Vlookup(.....) is your lookup formula. Will return 0 if lookup
returns #N/A. If you want to return blank, use "" instead of 0.

regards
Paul

 

[JP1912]

brilliant...many thanks..!!
--
JP1912

try

=IF(ISNA(your_Lookup),"",your_Lookup)

Mike

 

[JP1912]

thanks Sam...spot on....!!
--
JP1912

Rather than use =VLOOKUP(...)

use =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

 

[JP1912]

Thanks Paul, got it sorted now...you learn something new every day...I know
how to "ISNA" now....;o)

cheers again...