Dammit, VLOOKUP

posted Nov 8, 2011, 2:36 PM by Ezra Kenigsberg   [ updated Oct 14, 2013, 8:33 AM ]

#DataTipOfTheDay 2011-11-08

Q:    "Why does Excel's VLOOKUP() function do approximate matches by default?"

A:    Sheesh, I wish I knew. Seldom do we want VLOOKUP() to do approximate matches; exactly 87% of the time, we want exact matches.

Background for anyone who's lost:
  • VLOOKUP() is a great spreadsheet function that looks things up in a table. Here's a pretty good demo. 
  • VLOOKUP() takes three required arguments and a fourth, optional, argument. The optional argument is damn important and really ought to be required.
  • The optional argument determines whether VLOOKUP() strives to make exact matches, or settles for approximate matches.
  • Because the "settle for approximate matches" option has the potential to ruin your week, it shouldn't be the default option. But it is. Ugh.
  • Once you know VLOOKUP(), you're no longer a beginner; you are now an intermediate spreadsheet jockey. Congratulations!
Two possible solutions:
  1. My colleague Prashant Nambisan created a clever formula that does the lookup. Fast! No code needed!
  2. I wrote a simple add-in for a spreadsheet function called ExactVLookup(). Click the link to save it to your hard drive.*
ExactVLookup differs from VLOOKUP in two ways:
  • it doesn't return approximate matches; and
  • it can return a value to the left of the column being looked up.
The ExactVLookup syntax:
=ExactVLookup(value, lookuprange, offset)
  • value = the value you want to look up
  • lookuprange = the column in which you want to look up value 
  • offset = the number of the columns that ExactVLookup should return (1 = the lookuprange column)
Share and enjoy! As always, the MIT license applies.
******************************************************************************************
ExactVLookup
Copyright © 2013 Ezra Kenigsberg

Permission is hereby granted, free of charge, to any person obtaining a copy of this
software and associated documentation files (the "Software"), to deal in the Software
without restriction, including without limitation the rights to use, copy, modify, merge,
publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons
to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or
substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE
FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR
OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
DEALINGS IN THE SOFTWARE.
******************************************************************************************
Please contact me with comments/bug reports/suggestions: acumen@gmail.com
******************************************************************************************

*    I recommend saving XLAs to
  • C:\Documents and Settings\[username]\Application Data\Microsoft\Excel\XLSTART (for Win XP) and
  • C:\Users\[username]\AppData\Roaming\Microsoft\Excel\XLSTART (for Win7 and Win8). 
XLAs in that directory automatically open when I start Excel. I created a subdirectory called "Inactive" that I move my XLAs into when I want to deactivate them.

I don't know what the corresponding directory is for Mac yet--let's see how long it takes for a Mac user to read this footnote and drop me a line and let me know what it is (I'm writing these words on November 8th, 2011).
Comments