Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Question 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.

Thank you for your help!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 829
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup then interpolate,return corresponding value, if not, return other value Wzaman Excel Worksheet Functions 1 December 14th 10 02:17 AM
VLOOKUP return #N/A William Beard Excel Programming 4 January 25th 09 12:01 AM
VLookUp - Does the VLookUp return the exact information? Cpviv Excel Worksheet Functions 2 October 28th 08 10:57 AM
VLOOKUP return - #N/A Tim Excel Programming 8 February 5th 04 02:10 AM
VBA Syntax for VLOOKUP to return array of return values Alan Beban[_3_] Excel Programming 7 August 5th 03 11:41 AM


All times are GMT +1. The time now is 03:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"