VLOOKUP with Approximate Match

Quick reference

• 00:04 One of the most powerful functions inside Excel is
• 00:06 VLOOKUP() and it scares a lot of users
• 00:09 probably because there's two different ways to use it and they don't always know which way to go with it.
• 00:14 What we're going to focus on here is using VLOOKUP with an approximate match.
• 00:18 What that means is that it's if Excel is looking for something in
• 00:21 a table and it can't find the exact item it's looking for it will guess to try and find the closest value or amount.
• 00:29 So in this particular case we've got a nice little table here that shows some quantities
• 00:32 and the price per unit that you could get if you order in a volume quantity.
• 00:36 We obviously don't have a table that shows every variation of 5001, 5002, 5003, and this is where VLOOKUP with an approximate match
• 00:44 really shines is when it has these sort of bands you have to look through.
• 00:48 It works kind of like the old classic game show I guess you could say, like The Price Is Right
• 00:51 where you want to try and get closest to without going over and that's what Excel will return for you.
• 00:56 Now in this case we have a quantity here of 5798 and we want to try and find
• 01:01 the price per unit for each of these particular items if we order that quantity.
• 01:05 So this is where we are going to reach to VLOOKUP to do it. So we'll say =VLOOKUP
• 01:10 and it's going to ask for a lookup value. Now this is where really it's asking what would you like me to look up?
• 01:17 So we're going to click on cell B3 and that is the lookup value that we're actually after,what would we like to look up?
• 01:25 Next Excel asks you for a table array and this really in English means
• 01:29 where would you like to look it up? Where is the address of that table I'm trying to find?
• 01:34 So we'll go and highlight the area that we're looking for so from A8 all the way down to B14.
• 01:40 The next thing it says is col index number so that's which column would you actually like to return if I find a match?
• 01:48 In this particular case we want the second column.
• 01:51 Now the second column is not column one, column two according to the spreadsheet. What that is is its column one in your data table or
• 01:59 column two in your data table here. That's important because if this data table actually started in column B it was B and C
• 02:07 I would still be looking for number two because I want the second column in
• 02:11 the data table not the second column in the spreadsheet it's a real key distinction there.
• 02:16 Now the last parameter here is actually optional but I would
• 02:21 really encourage you to try and declare this anyway because if you forget and you're trying to do a false for an exact match its a
• 02:28 problem. You always want to be in the habit of trying to declare so you explicitly know what you're actually
• 02:33 after. In this case what we're going to go with is true for an approximate match.
• 02:37 And what that essentially means in English is if Excel can't find what you've asked it
• 02:42 to in the data table would you like it to guess and try and come back with a closest match?
• 02:48 So guess equals true in this particular case. When I close the bracket here and hit Enter
• 02:54 you'll see it comes back with $5.80
• 02:56 and if we look down our table we can say that, yup the quantity is definitely more than one.
• 03:01 The quantity is more than 5000 but it's less than 10,000 so that means we're going to be in this band here
• 03:06 and the price is $5.80. If I went and changed this to
• 03:11 10,091 it would come back to $5.65 because it's over 10,000 that's the $5.65 level
• 03:17 but is less than 20,000.
• 03:20 Now real big key, I'm just going to slide that one back for a second, really big key to working with VLOOKUP and approximate match is that your
• 03:28 data, your look up column over here must be sorted.
• 03:32 It doesn't matter if you're using alphabet, doesn't matter if you're using values it has to be sorted
• 03:37 in ascending order. If it isn't then what Excel does is it actually can
• 03:43 mess things up. Now watch this: so if we change this to 10,000 and we change this to 5000
• 03:48 what do i come back with? I come back with a price of $5.95.
• 03:52 And the reason being is because it's looked at this and said 5798 is greater than 1, going to
• 03:57 move up to the next row and oh, it's less than 10,000 guess I'd better stop looking and it'll go back.
• 04:02 So really, really important whenever you're working with an approximate match
• 04:07 you would definitely want to have these things in ascending order.
• 04:11 An exact match, not necessarily but an approximate match absolutely critical.

Lesson notes are only available for subscribers.