A week ago, Dick Kusleika posted an excellent article outlining some Formula Tips. I've already made plans to steal and use his ideas in my training. But my plagiaristic habits are not the reason for this post.
?
.
In the comments of Kusleika’s post, I astutely pointed out that:
"When I use VLOOKUPS, I replace Range_Lookup argument “FALSE” with 0:
Instead of this: VLOOKUP(A1,Sheet2!A1:C100,3,FALSE)
I use this: VLOOKUP(A1,Sheet2!A1:C100,3,0)"
.
Throughout the course of the comment thread, it was agreed that the leaving off the FALSE or the 0 from a VLOOKUP formula would essentially default the Range_Lookup argument to True.
.
So if you entered this formula: VLOOKUP(A1,Sheet2!A1:C100,3)
It’s the same as this: VLOOKUP(A1,Sheet2!A1:C100,3, TRUE)
.
Now, I’ve worked with Excel for a long time. I can count on one hand the number of times I’ve had to use TRUE as the Range_Lookup argument in a VLOOKUP instead of FALSE. I’m confident that this is the case for a majority of users out there.
.
So why is the TRUE the default? This is what I like to call a Defunct Default – a default that doesn’t jive with a majority of real world scenarios.
?
.
Another example of a Defunct Default can be found in the MATCH function.
When using a MATCH function, you need to indicate a Match_Type as the last argument.
.
1 finds the largest value that is less than or equal to lookup_value
-1 finds the smallest value that is greater than or equal to lookup_value
0 finds the exact match.
.
If you leave off the Match_Type Argument, a Match Type of 1 the default.
.
So if you enter this: =MATCH(39,B2:B5)
It’s the same as this: =MATCH(39,B2:B5, 1)
.
Unfortunately, the vast majority of the scenarios where I needed the MATCH function, I needed to match Text, where an exact match is required (Match Type 0). With the default, matching any text would yield a result no matter what text I entered. I need to be able to match only if the text exists in my lookup array.
In terms of a real-world default, why wouldn’t an exact match be the logical choice? How exactly did 1 trump -1 as the default anyway?
?
.
I’m too lazy to think of more examples of defunct defaults. Can you think of any?
Remembering back to the days of early versions of Lotus 123 I'm pretty sure that vlookup didn't have the exact match option, ie you always had to sort your source data and got the closest match. Can't remember whether this was the case for early versions of Excel though. But maybe when they introduced the exact match option they wanted backward compatibility.
DaveM: Good point. I bet your'e right. Backwards compatibility probably had alot to do with the placement of defaults.
I had never thought of that.
However, it's different if you put the comma at the end w/o parameters.
This VLOOKUP(A1,Sheet2!A1:C100,3,FALSE)
Would be the same as this VLOOKUP(A1,Sheet2!A1:C100,3,)
Regards
Elias: How incredibly strange. I did not know that.
When using match in VBA…
if the list is sorted, then a match type of 1 or (-1) is much faster. It can even be faster when sorting the list, matching and then resorting the list.
Very interesting stuff. Thanks alot.
Interesting article. I find it also very interesting that always the "1" is the default value (in the MATCH and VLOOKUP functions described above).
Maybe it's easier to program our lovely spreadsheet application with 1 as a default option for "everything"
I think it's actually a good thing that the MATCH and VLOOKUP last argument defaults to 1. That avoids a lot of error messages for people who don't know how to use the functions properly. Leaving out the last argument will always provide them with a value, and if they don't cotton on to the fact that it may actually not be the correct result, well, that's what they deserve.
It helps conquer the learning curve if you hit your toes hard a few times with that situation.
I actually prefer writing out the FALSE or TRUE in VLOOKUP, rather than using 1 or 0. Although I have to type a few more characters, it's not such a big deal, and with the parameter suggestions that Excel 2007 and later pops up, it's just a "down arrow" and "tab" to complete, so only one key stroke more than typing a 1 or a 0.
In a long and nested formula I appreciate all the help I can get when I need to go back and review or rework it, so if I see a FALSE or TRUE jump out at me from a VLOOKUP, I much more readily recognise what the VLOOKUP is supposed to be doing. If it were 0 or 1, I might need to look a bit harder to see what the formula does.
Then again, I always use 0 or 1, not TRUE or FALSE with MATCH.
But I NEVER, EVER omit it.
There should be an Option Explicit for functions that ensures that all parameters are spelled out.
How odd that TRUE should be 1, rather than -1?
I often use the TRUE argument with a VLOOKUP to bracket calculated items – e.g. when ageing something, then group into an appropriate category. Looks like Match would do the same thing for me – is either appreciably quicker?
teylyn – I think that's rather a brutal approach!