VLOOKUP & other lookup functions: A proposal to MicroSoft
Posted by Aladin Akyurek on April 02, 2001 6:59 AM
The proposal that follows applies also to other lookup functions.
1. When the lookup value is not available in the lookup range VLOOKUP should return "" instead of #N/A, a result where =ISBLANK(result) is TRUE.
Two reasons:
a) Almost all users appear to want such behavior from this function, whence =IF(ISNA(VLOOKUP-formula),"",VLOOKUP-formula).
b) #N/A is not an error value as the others are (like #DIV/0!, #VALUE!).
2. A richer syntax for VLOOKUP like
=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})
{} means optional; the default value for 5th argument is a blank ("").
Example:
=VLOOKUP("Doreen",STUDENTS,3,0,"")
ADVANTAGE: No more "compute twice".
What do you think?
Note. I hope Mark too (Mark W, that is) will comment.
Aladin
Posted by Dave Hawley on April 02, 2001 12:27 PM
My earlier assessment of number of ISNA() evaluations
for =IF(ISNA(VLOOKUP(E1,$A$1:$B$5,2,0)),"",VLOOKUP(E1,$A$1:$B$5,2,0))
was incorrect. There would only be 1 per lookup_value. Thus,
the relative efficiency of the two approaches would be
governed primarily by the VLOOKUP() "double-whamy".
This performance difference is a function of the
ratio, (# of table rows)/(# of lookup_values), and
increases as this ratio increases. Looking up a
single value against a 1 row table using
=IF(ISNA(VLOOKUP(E1,$A$1:$B$5,2,0)),"",VLOOKUP(E1,$A$1:$B$5,2,0))
would require 1.5x the number of operations. Using
the same formula for a single lookup against a 65,000
row table would require nearly 2x the number of
operations.
Posted by Mark W. on April 02, 2001 12:40 PM
First, I seriously doubt that Microsoft would make "" the default return value. Because
if would affect current usage in an installed base of worksheets. Secondly, there's really
no need for "compute twice" when the #N/A is handled by SUMIF, etc.
Posted by Mark W. on April 02, 2001 12:50 PM
For more on this topic see my posting...
Posted by Aladin Akyurek on April 02, 2001 1:31 PM
Mark - The installed base wouldt not have much to fear: they can specify #N/A as the return value, if their models depend on this specific value. Besides the SUMIF etc would still function if "" is returned instead of #N/A. It's addmittedly hard to imagine what system of formulas would break.
I've seen your #N/A-post (it even seems to appear within this thread!). The need for such lengthy explanations would vanish under the proposal. A happy outcome, no? First, I seriously doubt that Microsoft would make "" the default return value. Because
Posted by Mark W. on April 02, 2001 1:59 PM
> Mark - The installed base wouldt not have much
> to fear: they can specify #N/A as the return value...
Microsoft wouldn't want to make such a change if it
forced users to revise their existing worksheet logic.
This would be a public relations fiasco. A case in
point... VLOOKUP() didn't always have a 4th, optional
argument. VLOOKUP() just behaved as it does now with
TRUE as its 4th argument. So when, they permitted
FALSE, the default behavior was the "old" behavior. : First, I seriously doubt that Microsoft would make "" the default return value. Because
Posted by Mark W. on April 02, 2001 2:29 PM
Another consideration...
Also... Returning "" wouldn't help if the formula
that referenced the results of a VLOOKUP() used
arithmetic operators. =3+"" produces a #VALUE!
error. You end-up doing the error handling anyway
using =3+N(""). > Mark - The installed base wouldt not have much
Posted by Aladin Akyurek on April 02, 2001 2:42 PM
Re: Another consideration...
This is a good one.
Don't you expect the users who have =3+VLOOKUP... like formulas have already built into them error-handling of the type ISNA, etc., if what VLOOKUP returns is not guaranteed to return a number? I reckon the error-handling involved for non-numbers would still work under the proposal.
Posted by Mark W. on April 02, 2001 3:03 PM
Re: Another consideration...
> Don't you expect the users who have =3+VLOOKUP...
> like formulas have already built into them error-
> handling of the type ISNA, etc., ...
Yes, I do... and I go a bit farther... I expect
similiar error handling for all VLOOKUP() usage...
that's why I object to the use of
=IF(ISNA(VLOOKUP()),"",VLOOKUP()).
