Remember Me?

#1
August 29th 13, 11:20 PM
 Junior Member First recorded activity by ExcelBanter: Aug 2013 Posts: 1
Vlookup to return 0 instead of NA

I am hoping someone can help me....

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. I am using the results in calculations and really need it to return either a 0 or be blank.

#2
August 30th 13, 12:49 AM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Wzaman Excel Worksheet Functions 1 December 14th 10 02:17 AM William Beard Excel Programming 4 January 25th 09 12:01 AM Cpviv Excel Worksheet Functions 2 October 28th 08 10:57 AM Tim Excel Programming 8 February 5th 04 02:10 AM Alan Beban[_3_] Excel Programming 7 August 5th 03 11:41 AM

All times are GMT +1. The time now is 05:51 AM.

The comments are property of their posters.