# Vlookup to return 0 instead of NA

Discussion in 'Excel Beginners' started by CYA30, Aug 29, 2013.

1. ### CYA30Guest

I am hoping someone can help me....

I am currently building out a spreadsheet and have successfully (
hope!) used this formula
=VLOOKUP(MROUND(\$D\$13,2),Sheet2!\$A\$1:\$H\$353,2,TRUE). It's returning th
numbers that I need. What I would like though is for it to return a
instead of N/A when the information is not available. I am using th
results in calculations and really need it to return either a 0 or b
blank.

--
CYA30

CYA30, Aug 29, 2013

2. ### joeu2004Guest

"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\$353,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\$353,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.

joeu2004, Aug 30, 2013