Locked lesson.

Upgrade

About this lesson

Learn to use VLOOKUP to find an approximate match and return it or the corresponding value from another column.

Exercise files

Download this lesson’s related exercise files.

VLOOKUP Approximate - Begin.xlsx
27.3 KB VLOOKUP Approximate - Complete.xlsx
27.3 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.
Login to download
  • 00:04 In this video,
  • 00:05 we're going to explore one of the most feared functions in all of Excel, VLOOKUP.
  • 00:10 And the reason it's so feared,
  • 00:12 is mainly because most people don't really understand how it works.
  • 00:15 Especially given that it can work in two separate ways,
  • 00:19 depending on what your final parameters that you give.
  • 00:22 Now, the challenge with this function here,
  • 00:24 is that it has everything in it a function can possibly throw at you.
  • 00:27 And for that reason, I also believe that it's one of the most important functions
  • 00:30 to really learn in Excel.
  • 00:31 Because if you can get your head around VLOOKUP, any other formula is just syntax.
  • 00:36 So what does it actually do?
  • 00:38 Well, the purpose of VLOOKUP is to take something like a value, like 40,
  • 00:43 look it up in a table of data by looking down the first column.
  • 00:47 And if it finds a match, return a specific offset back to the cell.
  • 00:53 The challenge with VLOOKUP is it can work in two separate ways.
  • 00:56 We can return what we call an approximate match,
  • 00:59 the subject of this video, or an exact match.
  • 01:02 What is an approximate match?
  • 01:04 An approximate match is where we have gaps in our data.
  • 01:07 So when we're looking for four years in business, for example,
  • 01:11 notice that four is not in the table, and we need to somehow return a result anyway.
  • 01:16 Now, the first one that we're going to start with here,
  • 01:19 is we're going to start with owner's age.
  • 01:21 And the idea here is we're going to look down this table, find 40,
  • 01:24 and return a score for a credit application.
  • 01:26 So we want to get 7.5.
  • 01:29 And the way this is going to work,
  • 01:30 is we're going to start typing our function VLOOKUP.
  • 01:33 Once we actually do this and we go open our parentheses here, it asks for
  • 01:37 lookup_value.
  • 01:38 What does that mean?
  • 01:39 It means what do you want to find?
  • 01:42 Well, I want to find 40.
  • 01:46 Next, it says, give me table_array.
  • 01:49 Now, what is that all about?
  • 01:50 Well, what the table_array is asking for, is asking for
  • 01:53 the range of data that you're going to look in.
  • 01:55 And it needs to be a tabular setup here.
  • 01:57 So the range that I'm going to look at is,
  • 01:59 I'm going to look at this table of ages and scores.
  • 02:01 So we're just going to fill that in here.
  • 02:04 The third required parameter is column index number, and this is the tricky part.
  • 02:10 VLOOKUP always looks down the first column, okay?
  • 02:13 And that is, no matter where it is in your spreadsheet, column one.
  • 02:17 So in this case E is column one.
  • 02:21 The second column is F.
  • 02:23 But we will actually refer to this as column two and
  • 02:26 that's the column that holds the value we want to return.
  • 02:29 We want to find 40 and then return the offset in column two.
  • 02:34 Now, there's one final parameter of VLOOKUP.
  • 02:36 It is actually optional, however I recommend that you always,
  • 02:41 always declare this.
  • 02:43 By default it will default to true.
  • 02:45 However, we want to make sure that you get in the habit of always declaring it
  • 02:49 anyway.
  • 02:50 And the reason being, is because this will make absolutely certain that you
  • 02:53 are writing very explicit formulas, and you know what it's accomplished in here.
  • 02:56 True will give us the approximate match.
  • 02:59 So what I'm going to do is, I'm going to hit Tab here to commit my true,
  • 03:03 going to close my parenthesis and hit Enter, and we get 7.5.
  • 03:07 Great, let's do Years in Business.
  • 03:10 Now, the problem is of course, Years in Business has a gap.
  • 03:13 What we want to do, though, is we want to return a credit score.
  • 03:16 And what we actually want here, is we want to return a credit score of five.
  • 03:22 The reason being, your business has to reach five years before you're entitled
  • 03:26 to the score of 7.5.
  • 03:28 We don't want to give a score of 7.5 to a business that hasn't been in business for
  • 03:32 the full five years.
  • 03:33 So how do we make that happen?
  • 03:35 This is where VLOOKUP approximate match is magic.
  • 03:39 We're going to go with VLOOKUP.
  • 03:41 What do we want to look up?
  • 03:42 Four, where do we want to look it up?
  • 03:46 In the table here, and I always recommend you put your headers on it.
  • 03:50 Which column do we want to return if we find a match in the first column?
  • 03:54 We want to return the value from the second column.
  • 03:58 And would we like to use an approximate match that can handle gaps in the data?
  • 04:02 Yes, we would.
  • 04:03 So we're going to put in true.
  • 04:05 And what you'll see is that we do indeed get a score of 5.
  • 04:09 If we were to give them 5 years in business, they'll get a score of 7.5.
  • 04:14 So that works out quite nicely.
  • 04:16 Now, we can also see that same thing happen here,
  • 04:18 because we've used an approximate match.
  • 04:20 What happens if the owner's age is 37?
  • 04:23 Well, we'll just change that, and away we go.
  • 04:24 We've now got a score of 5 credit points because, they're not 40 years old,
  • 04:29 so haven't earned the full setup.
  • 04:31 There's a couple of really important factors you need to be
  • 04:34 aware of when working with an approximate match.
  • 04:36 VLOOKUP always looks for its value in the first column.
  • 04:40 And the data must be sorted in order.
  • 04:43 If it's not, you're going to get some really weird results.
  • 04:47 And I can actually show you that by coming right here and changing this to 40.
  • 04:51 We'll hit Enter and notice that my score goes to 0, okay?
  • 04:55 So this is a real challenge here.
  • 04:57 The data must be ordered, and you also do not want any duplicates in your data
  • 05:01 if you're using an approximate match.

Lesson notes are only available for subscribers.

Gift this course