Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel lookups should have a return paramater if not found
I wont accept it.
INSTANCE 1:- Assume that you are looking a value in table array and the resulting Column doesnt 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 wont 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return array formula lookups in to one single cell | Excel Worksheet Functions | |||
If a value is found return a true/false | Excel Discussion (Misc queries) | |||
Arbitrary Lookups - return ALL found values | Excel Worksheet Functions | |||
Return all values found for criteria | Excel Discussion (Misc queries) | |||
Vlookup, return zero if not found | Excel Worksheet Functions |