Locked lesson.
About this lesson
Learn to use VLOOKUP to find an approximate match and return it or the corresponding value from another column.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
VLOOKUP with Approximate Match.xlsx26.7 KB VLOOKUP with Approximate Match - Completed.xlsx
26.7 KB
Quick reference
VLOOKUP with Approximate Match
Using VLOOKUP to find an approximate match (not exceeding the value looked for) in a data table and return it or the corresponding value from another column.
When to use
Using VLOOKUP with an approximate match is ideal for tables that have gaps in data, such as volume pricing or tax tables.
Instructions
VLOOKUP’s parameters (in English)
- =VLOOKUP([what should it look for],[where is the table to look in],[which column should it return a value from],[guess if it can’t find a match])
Key points with VLOOKUP and approximate matches
- For an approximate match, VLOOKUP’s final parameter must be set to TRUE.
- The first column of your data MUST be sorted in ascending order. (If it isn’t, you will get very strange results.)
- The column to return is the column of the table, not the column of the spreadsheet (if your VLOOKUP table starts in column B, then 1 refers to column B, as it is the first column in the table).
- VLOOKUP will return the first row that is not in excess of what you’re looking for.
- It is a best practice to declare the final parameter as TRUE, even though it defaults. (This will get you in the habit of declaring the last parameter, making it less likely to forget it when it needs to be FALSE.)
Syntax
- =VLOOKUP(5000,A1:B10,2,TRUE) Returns the value from the second column of the table where the record in column A is closest to 5000 without going over.
- 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.