VLOOKUP with Approximate Match

Quick reference

• 00:04 In this video, we're gonna look at VLOOKUP, and
• 00:07 this is actually the first of two videos on this subject.
• 00:10 This function is, in my opinion,
• 00:12 one of the most important Excel functions you could possibly learn.
• 00:16 It is because it has everything in it that a function can possibly throw at you.
• 00:20 It's also one of the functions that terrifies most users because they
• 00:23 don't really understand how it works.
• 00:26 Now, in this video, we're gonna show you just
• 00:28 one of the two methods in which VLOOKUP can actually calculate.
• 00:32 And this is the one where it actually is used to return an approximate match.
• 00:36 So let's look at the first part of this, what does VLOOKUP actually do?
• 00:40 Well, the point of it is to look up a value, like the owner's age here, 40,
• 00:44 in a table of values, over here.
• 00:47 And what we're gonna do is we're gonna look down the first column until we find
• 00:50 40, and return the offsetting value.
• 00:54 But VLOOKUP can also be used when there's gaps in the data.
• 00:58 So in this case, if we have four years in business and
• 01:01 we look over in the Business Age, you'll notice that the value 4 does not show up.
• 01:05 So what we want to find here is,
• 01:07 we want to find the closest thing without going over.
• 01:09 So that would be 3, which would return a score of 5, not 5,
• 01:12 because that's higher than what we actually get here.
• 01:15 And we don't really wanna give 7.5 credit points until somebody's
• 01:19 actually earned an entire five years of business.
• 01:22 So let's see how we actually build this.
• 01:25 We're gonna go back over here to our score in C10 and we're gonna type in =VLOOKUP.
• 01:31 Now, the first thing it asks you for
• 01:33 is a lookup value, this is what would you like to look up in your table?
• 01:37 So we're gonna choose 40.
• 01:39 We're then gonna say comma, and it says table array.
• 01:43 What does table array actually mean?
• 01:45 It means where is the table of data that you want to look in?
• 01:49 So we're gonna say well,
• 01:51 let's look in this little table here that has age and score.
• 01:53 So we'll pull the headers in as well.
• 01:56 We're then gonna click comma and it says column index number.
• 02:00 What this means is, VLOOKUP always looks down the leftmost column and
• 02:05 what you give it, and then it says, when I find a match or the closest thing in that
• 02:09 first column, which column would you like me to pull back?
• 02:14 Now, the key on this is that the column count starts at one.
• 02:17 So column E is gonna be number one.
• 02:19 And the column two, which has the score in it, is actually gonna be 2, here we are.
• 02:24 And finally, there's a final parameter on this, which is called a range lookup.
• 02:29 Now this is basically, would you like to use an approximate match or
• 02:32 an exact match?
• 02:33 And in this case, even though it's optional,
• 02:35 I highly recommend that you declare it.
• 02:37 If you're looking for an approximate match, we're gonna go with true.
• 02:41 We're gonna close the parenthesis, and now, when we hit Enter,
• 02:44 you'll notice that it gives a score of 7.5, just as we would expect.
• 02:50 So that was easy.
• 02:51 Let's go and do the years in business.
• 02:53 We'll say =VLOOKUP.
• 02:56 What do we want to look up?
• 02:59 The number of years in business.
• 03:01 Where do we want to look it up?
• 03:03 In our table of data.
• 03:06 Which column would we like to return if we find a match or
• 03:09 the closest thing in our first column?
• 03:12 We'd like to return the second column.
• 03:14 And finally, our optional, but we really should declare it parameter is,
• 03:18 would we like an approximate or an exact match?
• 03:21 So we'll go with comma TRUE for an approximate match.
• 03:24 And we'll close our parenthesis and hit Enter.
• 03:26 Now, the difference between these two, our owner's age,
• 03:30 look down the first column, and it found 40, and gave us our offset.
• 03:35 But when we looked for the years in business, down the Business Age column,
• 03:39 there is no 4, so it said 3 is the closest thing without going over.
• 03:43 So we'll assign a score of 5, and there you go.
• 03:48 What would happen if we changed our owner's age to 37?
• 03:52 It gives us 5 because they haven't hit 40 to get a full credit point score.
• 03:59 Now, one of the key things that you have to be aware of when working with VLOOKUP
• 04:03 is that the data in your first column,
• 04:05 if you're using an approximate match, must be sorted.
• 04:08 And we can actually go and mess things around here by doing things like this.
• 04:13 If I were to put in a value of 40 in here, you'll notice that it suddenly comes
• 04:17 to 0 even though 37 should probably fall between these two things here and
• 04:22 this is the real challenge.
• 04:23 So, always remember, when you're working with VLOOKUP, if you're working with
• 04:28 an approximate match, make sure that your first column is sorted in ascending
• 04:33 order and also has no duplicates if you want to get the right result back.

Lesson notes are only available for subscribers.