VLOOKUP with Exact Match

Quick reference

• 00:04 This video is going to show the alternate method using VLOOKUP,
• 00:09 which is the exact match.
• 00:11 And what you'll see here is,
• 00:12 we have a table of flight numbers with destinations and costs.
• 00:16 But you'll also notice that the data here is not sorted.
• 00:20 Now, this has bigger issues than just being not sorted but
• 00:23 we already know that if we were to go and try and rate a VLOOKUP, and
• 00:28 we were to try and look up the specific value in this table, and
• 00:33 pull back a second column, and we were to say, true for an approximate match.
• 00:40 We're gonna get Vancouver for a destination,
• 00:41 which is not the right answer because it should be Halifax.
• 00:45 But here's the really scary thing.
• 00:47 If you take that final parameter off, it returns an approximate match by default.
• 00:53 And this is definitely not what we want.
• 00:56 Now notice that we've written our VLOOKUP.
• 00:58 We're still looking up the value we want.
• 01:00 We're looking in the table we want and
• 01:02 we've told which column that we want to return.
• 01:05 But we don't want to see Vancouver.
• 01:07 And look what happens if we just change this.
• 01:10 To false, asking for an exact match.
• 01:14 Boom, it picks up Halifax just like that.
• 01:17 And this is the really confusing thing with when you look up is that it actually
• 01:20 calculates in two different ways depending on what that final parameter is.
• 01:24 If you leave it as comma true or
• 01:26 don't declare it, it will default to an approximate match.
• 01:30 If you put in comma false, what VLOOKUP now does is it doesn't look for
• 01:35 the closest thing without going over, it actually goes through and
• 01:39 looks at every single line item until it finds the one that actually matches,
• 01:44 and we can even prove this now.
• 01:45 I can come back to this data here and I could say, you know what,
• 01:48 why don't we go with the data sort and we'll sort this into ascending order.
• 01:52 Notice that Halifax still shows up, I can sort it into descending order and
• 01:57 Halifax still shows up.
• 01:58 So it's actually looking for
• 01:59 the specific value no matter what happens which is great.
• 02:04 So the big difference comma false looks at every item to bring across and
• 02:09 get the information you're looking for.
• 02:11 Comma true needs to have sorted data
• 02:15 because it tries to get the closest thing without going over.
• 02:17 That's the big key to remember.
• 02:19 But here's why people get really, really scared of VLOOKUP.
• 02:25 Let's put in a flight number that doesn't exist, 776.
• 02:29 And boom we get #N/A, this is the part that scares people,
• 02:31 because they say what's going on, why did it break, what went wrong?
• 02:35 Well #N/A stands for not available.
• 02:38 There is no flight number that's actually available for this, and
• 02:41 this is kind of an important piece here,
• 02:43 because it's giving us a message back to say I can't find what you're looking for.
• 02:47 Why is this important?
• 02:49 Well it's super important because, if you used an approximate match,
• 02:52 it would give you an answer.
• 02:53 It would come back and give you something.
• 02:55 Now, with the data sorted in descending order,
• 02:57 it probably wouldn't be a good thing, but it'll give you something.
• 03:01 And that's the problem that happens with an approximate match is it
• 03:04 always returns a value.
• 03:06 What happens if you're actually selling flights and
• 03:08 you give your customer a value that's way lower than what they should be paying?
• 03:12 They're gonna pull up their credit card as fast as they can and
• 03:15 make that transaction happen and hope that you don't notice.
• 03:19 What happens if you go through and you actually charge somebody $3500 for
• 03:22 a flight that should be 200?
• 03:23 Well believe me, customer service is gonna suffer because they are definitely gonna
• 03:28 call you on that so, either way bad things happen and this is where the approximate
• 03:32 match returning this #N/A value can actually help you well.
• 03:36 Some key things to remember about this approximate
• 03:39 that data must be sorted in the first column.
• 03:42 An exact match?
• 03:43 Doesn't have to be.
• 03:45 If the exact match can't find what it's looking for,
• 03:47 it will return the #N/A value.
• 03:49 If the approximate match can't find what it's looking for,
• 03:52 it will return you something, and this is the key to remember.
• 03:56 If you're looking for a specific piece, and
• 03:58 you have to have an answer, you want an exact match, and to be honest with you,
• 04:01 this is about what you want 95% of the time.
• 04:05 The approximate match is not and yet that's the defaults.
• 04:07 These are things you want to remember when working with these.
• 04:10 VLOOKUP has other answers and issues.
• 04:13 If I set this back for example to 777 and everything works.
• 04:17 Look what happens if I go and put an apostrophe in front of 777.
• 04:21 And turn it into text.
• 04:23 At this point, it still throws me a hash and error.
• 04:26 These look perfect, but one's a number, one's text.
• 04:30 If one's value has spaces in front of it or behind it and the other doesn't,
• 04:34 it won't match.
• 04:35 There's no case sensitivity issues, but
• 04:37 these are some of the challenges that actually show up with VLOOKUP.
• 04:39 It will find something that exactly matches on the other side, and
• 04:43 throw a #N/A if it doesn't.
• 04:45 If you understand this, you will unlock massive power and view lookup.
• 04:49 This is the biggest most important function to learn
• 04:52 because it works in two different ways and the default is usually not what you want.
• 04:57 If you can wrap your head around that piece,
• 04:59 at this point everything will fall before you in Excel formulas.

Lesson notes are only available for subscribers.