A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel lookups should have a return paramater if not found



 
 
Thread Tools Display Modes
  #1  
Old January 14th 10, 03:14 PM posted to microsoft.public.excel.worksheet.functions
LJ
external usenet poster
 
Posts: 16
Default Excel lookups should have a return paramater if not found

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
Ads
  #2  
Old January 14th 10, 03:30 PM posted to microsoft.public.excel.worksheet.functions
Yanick
external usenet poster
 
Posts: 23
Default Excel lookups should have a return paramater if not found

On Jan 14, 10:14*am, 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....mspx?mid=df3e....


I do not agree.

First of all, 0 (zero) is already used by Lookup as a return value if
the returned cell is blank. I do not see what will be the difference
if a other value then #N/A is returned for a no match. Also think
returning the closest value is not a good idea since "closest value"
can be interpreted in many ways (depending of the situation).

You might need to use a other function for your needs.
  #3  
Old January 14th 10, 04:35 PM posted to microsoft.public.excel.worksheet.functions
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default Excel lookups should have a return paramater if not found

I won’t accept it.

INSTANCE 1:-
Assume that you are looking a value in table array and the resulting Column
doesn’t have any value then the vlookup will result you 0 (zero) value.
Generally it means that the lookup value is present on that table array but
there is no value found on the result column.

YOUR SUGGESTION:-
But in your case you have suggested that vlookup or hlookup should return 0
(zero) value if the lookup value is not present in table array means then how
we can come to know whether the lookup value is there in the table array or
not? Since both (INSTANCE1 and YOUR SUGGESTION) will result the same 0
(zero) value.

You can use IF & ISNA function in your Vlookup formula to get out of this
issue.

Like the below:-
=IF(ISNA(VLOOKUP(C1,A:B,2,FALSE)),"",VLOOKUP(C1,A: B,2,FALSE))

But I won’t suggest the below one. But you can arrive the 0 (zero) result
by using Vlookup with ISNA & IF function when the lookup value is not present
in the table array.
=IF(ISNA(VLOOKUP(C1,A:B,2,FALSE)),0,VLOOKUP(C1,A:B ,2,FALSE))

All The Best!!!

--------------------
(Ms-Exl-Learner)
--------------------


"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

  #4  
Old January 14th 10, 05:14 PM posted to microsoft.public.excel.worksheet.functions
Luke M
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
return array formula lookups in to one single cell ivory_kitten Excel Worksheet Functions 4 September 26th 09 10:08 AM
If a value is found return a true/false lbeemer Excel Discussion (Misc queries) 4 December 3rd 08 10:30 PM
Arbitrary Lookups - return ALL found values baki Excel Worksheet Functions 8 May 13th 08 06:23 PM
Return all values found for criteria duketter Excel Discussion (Misc queries) 2 May 23rd 07 08:36 PM
Vlookup, return zero if not found molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM


All times are GMT +1. The time now is 04:27 PM.


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