#N/A to 0 ?

[uberathlete]

Hi everyone. I've got a spreadsheet with vlookup formulas and a number of cells return #N/A as they should. However, instead of #N/A, I would like 0 to appear. How can I do this? Any suggestions would be great. Thanks!

 

[Aladin Akyurek]

Hi everyone. I've got a spreadsheet with vlookup formulas and a number of cells return #N/A as they should. However, instead of #N/A, I would like 0 to appear. How can I do this? Any suggestions would be great. Thanks!

If you are on Excel 2007...

=IFERROR(LookupExpression,0)

Otherwise:

If you have installed the free morefunc.xll add-in...

=IF(ISNA(SETV(VLOOKUP(...))),0,GETV())

=IF(ISNUMBER(SETV(MATCH(...))),INDEX(...,GETV()),0)

Without morefunc...

=IF(ISNUMBER(MATCH(...)),INDEX(...,MATCH(...)),0)

=IF(ISNUMBER(MATCH(...)),VLOOKUP(...),0)

 

[uberathlete]

Hi everyone. I've got a spreadsheet with vlookup formulas and a number of cells return #N/A as they should. However, instead of #N/A, I would like 0 to appear. How can I do this? Any suggestions would be great. Thanks!

If you are on Excel 2007...

=IFERROR(LookupExpression,0)

Otherwise:

If you have installed the free morefunc.xll add-in...

=IF(ISNA(SETV(VLOOKUP(...))),0,GETV())

=IF(ISNUMBER(SETV(MATCH(...))),INDEX(...,GETV()),0)

Without morefunc...

=IF(ISNUMBER(MATCH(...)),INDEX(...,MATCH(...)),0)

=IF(ISNUMBER(MATCH(...)),VLOOKUP(...),0)

Thankfs for your reply Aladin. Just wondering, in MATCH(...) and VLOOKUP(...), should there be something in the (...) ?

 

[barry houdini]

You need to replace the ... in the parentheses in Aladin's suggestions with the specifics of your formula.

E.g. if you were using

=VLOOKUP(A1,B1:C10,2,0)

then you could replace this with

=IF(ISNA(MATCH(A1,B1:B10,0)),"",VLOOKUP(A1,B1:C10,2,0))

note: the match function only uses the first column of the range from the VLOOKUP function