Vlookup to return 0 instead of NA
"CYA30" wrote:
I am currently building out a spreadsheet and have successfully
(I hope!) used this formula
=VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE ). It's returning
the numbers that I need. What I would like though is for it to
return a 0 instead of N/A when the information is not available.
If you are using Excel 2007 or later and you do not require compatibility
with Excel 2003 or ealier, you can write:
=IFERROR(VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$35 3,2,TRUE),0)
If you require compatibility with Excel 2003 or earlier, ostensibly you must
write:
=IF(ISNA(VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$35 3,2,TRUE)),0,
VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE) )
or
=IF(ISNA(MATCH(MROUND($D$13,2),Sheet2!$A$1:$A$353, 1)),0,
VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE) )
However, note that VLOOKUP(...,TRUE) returns #N/A only when MROUND(D13,2) is
less than Sheet2!A1. I wonder if you really want VLOOKUP(...,FALSE). See
the Help page for the difference. But if you truly want VLOOKUP(...,TRUE),
you could write:
IF(MROUND($D$13,2)<Sheet2!$A$1,0,
VLOOKUP(MROUND($D$13,2),Sheet2!$A$1:$H$353,2,TRUE) )
PS: I suspect you can write MROUND(D13,2) instead of MROUND($D$13,2). In
fact, MROUND(D13,2) might be preferred, depending on if/how you might copy
the formula.
|