In the previous post (The Most Important Function You Will Ever Learn) we looked at the VLOOKUP Function in detail.
Now we’re going to take it up a notch, Emeril Lagassi style.
“So Shir, I’ve got a list of values and I want to know which range it falls under.”
Sounds like the perfect job for VLOOKUP Approximate Match!
If you haven’t already done so, take a few minutes and watch this tutorial on VLOOKUP first.
Okay, so remember the last argument which I said should just stay as False? Well, here’s what happens if you use TRUE, and why that’s important.
Suppose you aren’t dealing with a list of finite values like a product price list, a database of names and phone numbers, or a list of Simpsons characters.
Suppose instead you’re dealing with something that involves a range of possible values, like figuring out which tax bracket you belong to for example.
Which brings me to tonight’s WORD (anyone else miss The Colbert Report?). Just kidding, but it does bring us to this video.
In one of my first videos ever produced (can you tell?) I walk you through how to create a VLOOKUP Approximate Match to give you the next closest match, instead of giving you a nasty #N/A error.
Use VLOOKUP Approximate Match to look up an input value (that falls within a particular range) on a reference table, and spit out another value that’s associated with that range.
Used commonly for scientific experiments, when a resulting measurement falls within a particular range, and must be associated with a specific value or name for that range.
VLOOKUP Arguments Explained:
- lookup_value = What value do you want to use to find other values?
- table_array = Where is the lookup table of other values?
- col_index_num = Which column number (relative to the lookup table) is the data you want to find located in?
- range_lookup = Do you want an Exact Match (FALSE) or Approximate Match (TRUE)?
Note: Excel does not need columns 2 and 3 (the “High End” and “Range Description”), but I strongly recommend setting it up this way to make it easier to understand.
As a bonus, I’m also attaching a downloadable excel spreadsheet from the video so you can try it out for yourself:
If this was helpful, do me a favor and send this to someone who you think would benefit. You’ll be making their day, and mine!
Also, leave a comment if any part of VLOOKUP Approximate Match still mystifies you, or simply share a time when you used VLOOKUP Approximate Match and it made you happier than a kid on a snow day. Either way I’d love to hear from you!
Happy VLOOKUP Approximate Matching!
P.S. Got a quick Excel question? Click here for on call Excel help
P.P.S. Seriously, stop wasting hours on Google! Hop on a call with me and move on with your day!
Shir, very nice post. Here is my entry in Chicken Soup for the VLOOKUP Approximate Match Soul:
Lawyers keep track of the documents exchanged in litigation by applying “Bates numbers” to every single page, and the documents in a large commercial litigation can run into the millions of pages. For example, a party’s 10-K for a given year might have Bates numbers BigBank_00125008 to BigBank_00125066. Legal briefs, expert reports, and other documents often cite to these documents by Bates number. A problem arises when these citations cite to a Bates number that isn’t the first page of the document. The solution I have found to fixing such cites is to generate a list of all the documents in a database, by initial Bates number, and then to do an approximate VLOOKUP of each citation against that list. VLOOKUP returns the nearest (lower) Bates number, which is the first page of the produced document. So, for example, if my expert cites page BigBank_00125011 (the 4th page of the 10-K) ,VLOOKUP tells me that this page belongs to the document that begins BigBank_00125008. Works like a charm.
-Ben
Well done Ben! How much time does that save you? 🙂