VLOOKUP with Approximate Match

Quick reference

• 00:04 In this video we are going to look at VLOOKUP and
• 00:06 this is going to be the first of two videos on the subject.
• 00:10 This is, in my opinion, one of the most important Excel functions to learn,
• 00:14 because it has everything a function can throw at you.
• 00:17 It's also one of the functions that terrifies most users because they don't
• 00:21 always understand how it works.
• 00:23 So for the first one here what we're going to do is we're going to show one of
• 00:27 the methods of which VLOOKUP works which is using an approximate match.
• 00:31 And basically what that means is we have a value,
• 00:35 we can use VLOOKUP to look up the value in a table.
• 00:39 So in this case, we've got the owner's age.
• 00:41 We wanna lookup the value in this particular table and
• 00:43 return the offsetting column.
• 00:45 The challenge we have here is that 47 is not a value in the age of column and
• 00:50 therefore we need Excel to comeback with the closest thing for us and
• 00:55 that's where the approximate match comes in.
• 00:57 And basically what you can look at is if your data is sorted properly,
• 01:01 which it must be for an approximate match,
• 01:03 it kinda plays the game like the old Price is Right, where it actually looks up and
• 01:08 find the closest thing without going over and then returns the offsetting column.
• 01:12 So for this particular case here, we have a credit scoring form that we're building,
• 01:16 we have our owner's age of 47 and we'd like to look up in this table and
• 01:20 return the appropriate score based on that.
• 01:23 So what we're gonna do is we're gonna start by typing =VLOOKUP.
• 01:28 When we open our parenthesis, you can see that we have four different parameters.
• 01:32 We've got look up value, table array, call index number, and range look up.
• 01:36 And what these mean in English is, what would you like to look up?
• 01:41 Well, I'd like to look up the value of 47.
• 01:44 The next question where it says table array,
• 01:47 really means where would you like to look it up.
• 01:50 So this is the boundaries of my table, headers included,
• 01:55 age to score, so I'm gonna select that, E8 to F13.
• 01:59 We now hit the comma and it says call index number, so what that means is which
• 02:04 column would you like to return when I find a match in the first column.
• 02:07 Well, I'd like to return my value from column number two.
• 02:12 The final parameter I highly recommend that you always declare even though it
• 02:16 is optional it will default to true, but I would like to use true as my
• 02:20 final parameter to force it to be an approximate match.
• 02:23 And I say always force it because this way you know you'll never make the mistake of
• 02:27 not declaring it.
• 02:29 Embarrassing things happen when you forget to declare it and it defaults to true and
• 02:33 you meant to be false.
• 02:35 Not an issue in this case, but certainly an issue in a lot of others.
• 02:38 So what we'll do now is we'll close the parenthesis off.
• 02:41 So just to review here, we say VLOOKUP, what would I like to look up?
• 02:46 I'd like to look up the owner's age, 47.
• 02:48 Where would I like to look it up?
• 02:50 In this table.
• 02:52 Which column would I like to return when I find a match in the first?
• 02:55 I'm gonna return column two.
• 02:57 And then comma, would you like me to return you the closest thing if I
• 03:02 can't find the exact value you're looking for?
• 03:05 Yes, I would.
• 03:06 And we'll say OK.
• 03:07 And you can see what's gonna happen is it again, plays the Price is Right game.
• 03:12 It looks for the closest number, 47, so
• 03:14 47 the closest number is 40, without going over cuz 50 would be going over.
• 03:20 Once it finds 40, it looks up the value beside 7.5 and we can test this out.
• 03:25 Lets go 23, we'll get a score of 2.5.
• 03:28 Okay so different scores come back here.
• 03:32 Likewise we can do the same for our years in business, so once again,
• 03:36 we'll say =VLOOKUP.
• 03:38 What would we like to look up?
• 03:39 The number of years in business.
• 03:41 Where would be we like to look it up?
• 03:43 In the table over here.
• 03:46 Which column would we like to return when we find a match in the first column?
• 03:50 We'd like to return the second.
• 03:51 And would we like to use an approximate match or
• 03:54 come back with the closest thing if we can't find the number four exactly?
• 03:58 And yes, we would.
• 04:00 And there we go.
• 04:01 It brings us back a score of five.
• 04:03 Why five?
• 04:04 Because three is the closest thing without going over, and therefore the value here.
• 04:09 Will it return the exact value if we had 40?
• 04:13 Well yeah, because now 40 is the closest thing without going over.
• 04:16 So it'll definitely do that.
• 04:17 Some key considerations to remember with VLOOKUP,
• 04:21 it always looks in the first column and returns your offset to the right.
• 04:24 It can't go the other way around.
• 04:26 The other thing with an approximate match that's critically important is that
• 04:30 the first column of data be sorted in ascending order.
• 04:33 And I'll show you why that is right off the bat.
• 04:35 What if we put a value of 42 right here.
• 04:38 Watch what happens to our score.
• 04:41 You'll notice that it comes to 0 because now 0 is the closest thing without
• 04:45 going over.
• 04:46 Now it's not perfectly true, but this is true enough for
• 04:49 understanding what actually ends up happening here.
• 04:51 If you don't have your data sorted,
• 04:53 you are gonna get some very odd results coming back.
• 04:56 So make sure if you're using an approximate match that the data is
• 05:01 sorted ascending order by the first column always.

Lesson notes are only available for subscribers.