View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Luke M Luke M is offline
external usenet poster
 
Posts: 2,722
Default Excel lookups should have a return paramater if not found

Not quite what you stated, but in 2007 a new function "IFERROR" is
introduced. Does the same thing that Ms-Exl-Learner suggested, but a few less
arguements"
=IFERROR(Value, Value_if_Error)
=IFERROR(VLOOKUP(...),"")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LJ" wrote:

The VLOOKUP and HLOOKUP functions in excel return #N/A if the index is not
found in the search list. The parameters should allow you to return zero or
"" in these instances. This would remove the need for a great deal of
validation tests.

Ideally I would suggest the following syntax:

=VLOOKUP(A1,Table!A:N,5,NewParameter)

Where NewParameter could be a value, null, next (the closest value higher
than the search index) or previous (the closest value lower than the search
index)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions