VLOOKUP with Exact Match

Quick reference

• 00:04 In this video, we're going to focus on how VLOOKUP works with
• 00:08 an exact match rather than an approximate match.
• 00:12 Now, before we start, there's a couple of observations that I want to make
• 00:16 about my goals here and our data.
• 00:19 So the first thing is the data.
• 00:21 In an approximate match, the data must be sorted in ascending order.
• 00:25 You will notice that that is not the case here,
• 00:28 these numbers are not in ascending order, and there are definitely gaps in them.
• 00:32 Now the deal with this is though, is that I'm looking for something very
• 00:37 specific in this case, I want a flight number to pull back its destination.
• 00:42 If I put in a flight number that's not valid, I want it to know,
• 00:46 I don't want it to just come back and give me the closest flight that it thinks I
• 00:50 should have because these things are not ordered in any way.
• 00:53 So let's start off this process here and we're going to actually start by
• 00:57 writing an approximate match to show you what actually happens.
• 01:01 So we're going to go with equals VLOOKUP, I'm going to look up what?
• 01:05 My flight number.
• 01:09 Where am I going to look it up?
• 01:10 going to look it up in my data table.
• 01:13 Which column would I like to return if I find a match?
• 01:16 Well, I'd like to return the second column with the destination.
• 01:19 And what do I want?
• 01:21 Well, I'm going to go with an approximate match of true.
• 01:23 All right, so this point, if I go and put this in it tells me that
• 01:29 flight 777 is Vancouver even though flight 777 would appear to be Halifax.
• 01:35 This is really weird and this has everything to do with the fact that
• 01:39 the data is not sorted and we're using an approximate match.
• 01:42 So it actually, even though it has the value, may not find.
• 01:45 There's a technical reason behind this that we won't get into, but
• 01:49 the reality here is, this is not going to work for us.
• 01:52 It is also really, really important that I get an exact match because I need
• 01:56 to know that I'm getting the right one.
• 01:59 So let's go back and change this right away to, False.
• 02:04 And what this means is, keep looking until I find the exact value and
• 02:08 if I don't find the exact value, please tell me about it.
• 02:12 And when I go in and take a look, it now returns Halifax.
• 02:14 Okay, so this is the first thing that's important, if your data is not ordered,
• 02:18 you need to use an exact match.
• 02:20 But then of course, the challenge comes into, what about flight 776?
• 02:25 And if I hit Enter on this, you'll notice that it comes back with #NA.
• 02:29 Now this is a little bit scary but it's actually a good thing because what
• 02:35 that means is, it is not available, I have not been able to find a match.
• 02:40 Now, the thing is,
• 02:42 nobody likes seeing something that says NA in the middle of their worksheet.
• 02:47 So let me show you a quick trick that we can use with VLOOKUP in order to fix this,
• 02:52 because we have a cool little function here called IFNA and
• 02:56 we can wrap that around our VLOOKUP.
• 02:58 So I'm going to put IFNA open parenthesis right in front of my VLOOKUP,
• 03:03 what it does, it's going to run the VLOOKUP now and if it's not available,
• 03:09 we can feed back something like, NOT FOUND, as text between our quotes, okay?
• 03:15 And when I go and put that in, we now know Flight 776 is not found and
• 03:20 yet Flight 902 is found, okay?
• 03:23 So this is really, really important here,
• 03:26 I want this message back that says that this flight is not valid, and
• 03:31 this is where an exact match becomes very, very useful.
• 03:35 Now, there are some tricky things that can happen inside your VLOOKUP though,
• 03:39 where you run into a scenario where maybe things don't work the way you would
• 03:42 expect them to.
• 03:43 And I'm going to show you right now, I'm going to convert this little one here
• 03:47 with Flight 777 I'm going to put an apostrophe in front of it, and
• 03:50 what that does is it's actually going to convert it into text.
• 03:53 Now that's not very visible here because everything is left alone,
• 03:57 even though there's this tiny little indicator here.
• 03:59 Why is it important?
• 04:01 Well, because if I go and look up Flight 777 right now,
• 04:04 it tells me that it's not found.
• 04:06 But it's there, well, the thing is, if I look it up as text with
• 04:11 an apostrophe in front of it, it will then find me the destination.
• 04:16 And this is one of the tricky things that can actually happen that leads you into
• 04:20 this NA area is when there's a space at the end of the value being looked up or
• 04:24 a space in the value on the table, or one side of it is formatted as text and
• 04:28 the other side as a number.
• 04:30 Those things are not equivalent, so they can really actually throw you for
• 04:34 a bit of a loop.
• 04:34 This is an area where you definitely want to have consistency in your data.
• 04:39 It does not matter, obviously,
• 04:41 if the data is actually sorted here because we are using an exact match.
• 04:46 But you'll notice that this has a very different behavior than VLOOKUP now
• 04:50 because it's giving us the right answer, or with our nice little NOT FOUND error,
• 04:55 we can come back and we can actually tell us that something has not been found.

Lesson notes are only available for subscribers.