Replace N/A error with zero (0) without losing VLookup function

[J.F.Conway]

I have a set of data relating to a list of data tags that is regularly
updated to pivot tables with new data from related workbooks. The updated
data is supplied from an internal database (Oracle DB) that exports to Excel.
However, sometimes there is no new data associated to some of the data tags
listed and therefore, the database does not export the names of those data
tags where no new data exists on that update.

The problem is that when I update the pivot table (which has the full list
of data tags), where there is no data that month I keep getting the #N/A
error, because Excel cannot find the name of the data tag from the exported
data. This is causing me to have to manually delete the #N/A error in each
cell and replace it with a 0 (zero). This has a knock-on effect of deleting
the function from that cell, which means that the next time I update the
data, I need to re-enter that function into each cell with a 0 value.

Does anyone know how to add / alter a VLookup function to automatically
replace an #N/A error with a zero (0) value, without losing the VLookup
function? The function I use is:

=VLookup(cell refererence,'workbook filename.xls''worksheet',column,false).

Thanks.

 

[Jan Karel Pieterse]

Hi J.F.Conway,

=VLookup(cell refererence,'workbook filename.xls''worksheet',column,false).

Of course:

=IF(ISERROR(VLookup(cell refererence,'workbook
filename.xls''worksheet',column,false)),0,VLookup(cell refererence,'workbook
filename.xls''worksheet',column,false))

Or for 2007 only:
=IFERROR(VLookup(cell refererence,'workbook
filename.xls''worksheet',column,false),0)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[J.F.Conway]

Many thanks for your help, Jan Karel Pieterse. Problem fixed!!

Regards,

J.F.Conway