VLOOKUP with Exact Match

Quick reference

• 00:04 In this video, again we're gonna look at VLOOKUP.
• 00:06 But this time we're gonna look at using it in an alternate way.
• 00:09 Where the last video we looked at an approximate match,
• 00:13 this one we want to find an exact match.
• 00:15 So, basically what we're going to do is we're going
• 00:18 to look up this flight number in the table down here.
• 00:20 And if we can't find the exact instance of that flight number.
• 00:24 It's gonna return something that shows us an error,
• 00:26 rather than actually showing us the closest thing without going over.
• 00:30 The first things that I want to sorta draw your attention to
• 00:33 right off the bat here is that the data.
• 00:36 Remember vlookup always looks in the first column to try and find its match,
• 00:39 no matter what order it's in or whatnot.
• 00:41 And you'll notice that the data here is not sorted.
• 00:44 And this is important because it's not required to be
• 00:46 sorted when you're working with an exact match.
• 00:48 It is if you're working with an approximate match.
• 00:51 But in this case, it's not a big deal.
• 00:53 So what we're gonna do right now is we're gonna build a VLOOKUP function here.
• 00:56 We're gonna say equals VLOOKUP.
• 00:58 I'm gonna open my brackets.
• 01:00 And I'm gonna look up flight number 777.
• 01:01 And I'm gonna look that up in my table of data here.
• 01:06 And if I find the exact match of 777 in any of these flights
• 01:11 I'd like the return the destination which is column two.
• 01:15 Now, you might tempted to just close the brackets off and say enter.
• 01:20 And you'll notice it comes back with Calgary.
• 01:22 The challenge here, as you'll see is that flight 777 should actually be returning
• 01:27 Halifax, not Calgary at all.
• 01:28 And this is where it becomes important to actually
• 01:32 make sure that we're actually setting up with our data properly here.
• 01:36 If our data was sorted,
• 01:38 this would actually bring back the right result, but it's not.
• 01:41 So we want to say hey look we want to force this to actually looking in
• 01:43 the table till it finds the proper match.
• 01:45 Not return a guess.
• 01:47 And of course by omitting this last parameter,
• 01:49 it defaulted to true which was in the approximate match.
• 01:53 But not returning the right thing.
• 01:55 So let's go back to false.
• 01:57 Again, if it was an approximate match and the table was sorted in descending order,
• 02:01 that would have worked.
• 02:03 Because it's not sorted, and because we wanna make sure we're finding that
• 02:06 specific piece, we're gonna put on comma false.
• 02:08 And this says, don't guess.
• 02:09 Keep looking until you find what I'm looking for, or
• 02:12 return an error if you can't.
• 02:14 So with comma false, and we hit enter, it returns Halifax.
• 02:17 It keeps looking through the table until it finds what it actually needs,
• 02:21 which is great.
• 02:23 We say okay. Well that's kind of cool, but let's go and
• 02:26 copy this formula down here now.
• 02:28 And let's see what ends up happening.
• 02:30 And we get a pound and an a error.
• 02:32 We're going well, ok, so what's going on here, this doesn't make sense.
• 02:37 Let's take a look at our formula.
• 02:39 When we copied it we had a couple things that happened here.
• 02:41 Number one, what we're looking for, b four.
• 02:43 We hadn't set this as an absolute reference so this is gonna change on us so
• 02:47 let's go and say you know what.
• 02:48 Let's force this back and make sure that we're looking at the right thing here.
• 02:52 Another really common thing when people copy formulas is they have never locked
• 02:56 down the range of their VLOOKUP table with the absolute values.
• 03:00 And that means that the table shifts.
• 03:02 And that means that sometimes you won't find your data,
• 03:04 because it's not in the range you're looking at anymore.
• 03:06 So, really important, before you're gonna go and actually deal with these things.
• 03:10 What you need to do, I'm gonna go back to this original formula.
• 03:13 And I'm gonna set this to an absolute range by pressing four.
• 03:17 And I'm also going to set this to an absolute range,
• 03:20 because I'm always going to look at B three.
• 03:23 So, it doesn't change Halifax, and when we drag it down, we're gonna return Halifax.
• 03:28 In order to get the sales costs,
• 03:29 I just need to change the final parameter of comma three.
• 03:34 Which will now return the third column and return my value of 501.
• 03:39 As you can see, Flight 777 returns 501.
• 03:42 Now what happens if I don't find a flight number that I'm looking for.
• 03:47 Let's say we look for Flight 816 and we hit enter.
• 03:52 It's gonna come back with a pound and A.
• 03:55 Now this is one of the things that really scares a lot of people
• 04:00 when we're actually looking or using VLOOKUP.
• 04:02 They get back this pound and error and
• 04:03 they freak out because they don't understand what it means.
• 04:05 So I'll tell you what it means.
• 04:07 It's really simple.
• 04:08 It means it can't find what you're looking for, that's all it's saying.
• 04:11 It's just not a really pretty way of saying it.
• 04:13 What this means is.
• 04:15 I've looked through your entire first column of data that you've provided within
• 04:18 the range of the table that you've set up.
• 04:21 And I can't find a value that exactly matches what you showed me in B3.
• 04:26 Now sometimes this can be bizarre because you can look at it and say.
• 04:29 Well I can see my 777; I can see it in the table here.
• 04:32 But some things can really play havoc with you are trailing spaces, or
• 04:37 leading spaces on one side, or the other okay.
• 04:39 So those are things that can get in the way, or maybe your value is formatted.
• 04:44 It shows a 777, but it's actually 777.1 and it's actually not down here.
• 04:50 Those kind of things can mess you up.
• 04:52 So, those are things you wanna look for
• 04:53 when you're actually trying to debug your VLOOKUP.
• 04:56 Case sensitivity is not a problem.
• 04:57 What you're looking for
• 04:59 there, in the original table versus what you're looking up, are not problems.
• 05:03 But basically if it's not an exact match, it will return #NA.
• 05:07 So that's something to definitely be aware of.

Lesson notes are only available for subscribers.