VLOOKUP with Approximate Match

Quick reference

• 00:04 So in this video, we're going to look at using VLOOKUP with an approximate match.
• 00:09 And this is one of the most important functions that you can learn,
• 00:12 because it truly is the gateway function that will unlock
• 00:15 every other piece of Excel formulas, if you can really master how this one goes.
• 00:19 Now, the way VLOOKUP works, the intent of VLOOKUP,
• 00:23 is to take a value from a cell,and look it up in a table.
• 00:29 So, if we look here, we're looking for the age 47,
• 00:31 to look down the table to find the age of 47, and then return an offsetting column.
• 00:37 Now, the problem with this, and where we look at an approximate match here,
• 00:40 is that we don't see the value 47 in our table.
• 00:43 We see 40 and 50.
• 00:45 So, if you think back to the old American gameshow, The Price Is Right, when
• 00:49 somebody was bidding on the showcase, what they would do is they'd throw in a bid.
• 00:52 And the person that came to the closest
• 00:54 to the price of the showcase without going over was the winner.
• 00:57 And that's what we're trying to do here is play this game of
• 00:59 the closest without going over.
• 01:01 It's really, really important when you're using an approximate match,
• 01:04 when your data has gaps in here like we do between 0 and 21,
• 01:08 that your data is sorted in ascending order.
• 01:10 This is critical to making your VLOOKUP work if you're using an approximate match.
• 01:14 If you don't do this, you could get very strange results,
• 01:18 because Excel uses what's called a binary chalk method to actually figure out.
• 01:21 It doesn't actually look at the closest without going over.
• 01:23 It tries to dissect the range and try and figure out if it's higher or
• 01:27 lower or what not.
• 01:27 So your data's not sorted.
• 01:29 That can lead to some really weird things.
• 01:32 So, key piece, with an approximate match, the data must always be sorted.
• 01:36 The way VLOOKUP works is we type in =VLOOKUP, and
• 01:39 you'll notice when we open our brackets that we get a little bit of
• 01:43 a tool to text here, IntelliSende, and it says look up value.
• 01:47 So, what it's asking here's is what value would you like to look up in your table?
• 01:51 So, we're going to look up 47.
• 01:53 And when we hit comma, it says, what is the table array?
• 01:57 Now, the way that I look at VLOOKUP, when I look at the parameters here,
• 02:01 is I actually read it as what would you like to look up,
• 02:05 where would you like to look it up, which column would you like to return,
• 02:08 and would you like me to guess if I can't find it?
• 02:10 Now, that would be a better tool to text than what we see here.
• 02:13 But in this case, what are we looking up?
• 02:16 We're looking up 47.
• 02:17 Where are we looking it up?
• 02:19 We're looking it up in this table with ages through scores.
• 02:24 And then it says, which column would you like to look up?
• 02:26 And this is a number, and that's really important to understand here that this is
• 02:30 the second column of the table is the value we'd like to return, okay.
• 02:35 It's not Column F.
• 02:37 It's the second column in the table, and
• 02:39 that's important because this needs a number.
• 02:42 So, you say two.
• 02:42 And again, that does not indicate the second column in the worksheet.
• 02:46 It indicates the second column in the table of data that we're providing.
• 02:49 And then we have an option parameter at the end called range lookup.
• 02:54 And basically what this parameter is,
• 02:56 is would you like to guess if you can't find it?
• 02:59 In other words, would you like to return an approximate match?
• 03:02 So, what we're gonna do is we're gonna say comma true and close our brackets.
• 03:07 So, what we're saying here is we'd like to look up 47 in this table of data.
• 03:13 If we find it in the first column,
• 03:15 we would like to return the second column's match.
• 03:18 And please,
• 03:19 if you can't find exactly 47 in this table, return the closest match for me.
• 03:24 And what you'll see is when I hit return, it comes back with 7.5.
• 03:27 So, it's looked up the table.
• 03:29 It said, is it 47, 0.
• 03:32 No, okay, it's greater.
• 03:32 We'll keep on moving down the table.
• 03:34 Here I found 40.
• 03:35 That'll return me a value of 7.5.
• 03:37 50, oh, that's the greatest one.
• 03:39 That's gone over what I'm looking for, so I'm gonna default back to this 7.5.
• 03:44 Okay, now, VLOOKUP can also be used without declaring this last parameter.
• 03:49 So, if we say VLOOKUP, and this time I'm gonna look up the number of years in
• 03:52 business, and I'm gonna look it up over in this Business Age table.
• 03:57 And if I find a match in the first column,
• 04:00 I'd like to return the second column's score.
• 04:05 And this time, instead of declaring this final parameter, I'm gonna leave it off.
• 04:08 I'm gonna say Enter.
• 04:10 And you'll notice that it says 10, so it's looked all the way down the column.
• 04:12 It says, look here's 7, I found it.
• 04:15 There's 10. What if he was in business for six years?
• 04:18 Change this here, and it says, I'll give a score of 7.5,
• 04:22 because it's greater than 5 but less than 7, so this works out quite nicely.
• 04:27 There is no difference in the results between declaring the final parameter and
• 04:33 leaving it off.
• 04:34 So I could go back here and say, comma true.
• 04:38 And say Enter.
• 04:40 And this will work just fine.
• 04:41 This is something to remember is that the default for
• 04:44 VLOOKUP is to return an approximate match.
• 04:47 I would tell you, I'd highly encourage you to always actually explicitly declare your
• 04:51 final parameter as true or false.
• 04:53 And we'll get into false in a bit.
• 04:54 But the reason being is because this way we know you've made a conscious choice
• 04:59 to actually choose an approximate match.
• 05:01 So, this is the purpose of VLOOKUP and how it works with approximate matches.

Lesson notes are only available for subscribers.