VLOOKUP with Exact Match

Quick reference

• 00:05 In this video, we're going to look at an alternate to VLOOKUP, which is where we
• 00:09 use an exact match, and this is where we want VLOOKUP to return a specific
• 00:16 piece of information, and let us know if it can't find that piece of information.
• 00:21 You'll see that it works very similar to the VLOOKUP with approximate match.
• 00:25 We still start with =VLOOKUP.
• 00:28 We still say what would we like to look up.
• 00:31 We still say where would we like to look it up.
• 00:34 So our table, and again we still come back and
• 00:37 say which column would we like to return?
• 00:40 So in this case we're looking for destination, we'll say 2.
• 00:43 But this time, instead of saying true, we're gonna say false.
• 00:47 And this makes all the difference on how VLOOKUP actually works
• 00:50 in this particular scenario.
• 00:53 When I hit enter here, you'll notice that it comes back with Halifax.
• 00:57 So what VLOOKUP's doing is it's checking every single result
• 01:01 in the table this time.
• 01:02 It's not doing a closer without going over.
• 01:06 So in this case, it doesn't actually have to have the data sorted in ascending
• 01:11 order in the first column.
• 01:12 This is kind of an important thing.
• 01:14 As a matter of fact, let's go to data sort and
• 01:17 let's sort this from Z to A, so we've got it in reverse order.
• 01:22 You can see that this closest without going over game is definitely not
• 01:26 being played, because we're actually getting Halifax back.
• 01:29 So this is working quite nicely.
• 01:32 This is kinda cool.
• 01:33 Some other things that you want to be aware of is that whatever's in this cell
• 01:36 that's being looked up is not case sensitive with the first column here.
• 01:41 So if we had a list of animals and we had, some of the animals,
• 01:45 we were looking up a lower case value and all the animals were in upper case,
• 01:48 it would still return a match as long as it was spelled correctly.
• 01:51 So that's okay.
• 01:52 So some big considerations here.
• 01:54 Doesn't have to be sorted, doesn't have to be, or it's not, case sensitive.
• 01:57 Mind you, the approximate match isn't case sensitive either but
• 02:00 it definitely has to be sorted.
• 02:02 So what's so different about this anyway than?
• 02:04 Well here is the difference.
• 02:06 What happens now if I put in flight 776?
• 02:10 When I hit enter on this, it's going to return #N/A and
• 02:14 this is what users fear with VLOOKUP.
• 02:18 You'll almost never see a #N/A error when you're doing an approximate match.
• 02:22 Why?
• 02:23 It's because of that final parameter that says would you like me to guess
• 02:26 if I can't find it.
• 02:27 I'll give you the closest thing without going over.
• 02:30 With a VLOOKUP exact match, we don't do that.
• 02:33 With a VLOOKUP exact match, we come back and we say,
• 02:36 hey, if you can't find it, I need to know.
• 02:40 And you think about it.
• 02:40 I mean, if you're trying to give out prices for flights in this area and
• 02:44 you type in the wrong flight number and hit enter.
• 02:47 Well, what do you want to sell?
• 02:49 Do you want to actually know that that flight doesn't exist, or
• 02:53 do you want to just give out the cost for that flight?
• 02:55 Because here's what's gonna happen if you just give out the cost for
• 02:58 the closest flight that Excel matches.
• 03:00 If the flight is really cheap compared to what the list price is,
• 03:04 the customer's not gonna complain.
• 03:06 But if the customer gets a price that's exorbitant,
• 03:09 let's say it's a $3,500 fare for a flight that should be $400,
• 03:13 believe me your customer service is gonna suffer in a big way.
• 03:17 This is one of the reasons why we have to be very careful with our VLOOKUP issues or
• 03:23 with our VLOOKUP formulas, because if we forget to declare this final parameter of
• 03:29 comma false, it will default to comma true and
• 03:34 it will give me a value that really seems to make no sense.
• 03:39 Okay, so we don't want that.
• 03:41 What we want to do is we want to force ourselves to
• 03:44 always declare this final parameter.
• 03:45 if we want it to be true, let's make it true.
• 03:47 But if we don't, we want to say let's have this as false.
• 03:51 The challenge here is that sometimes we get errors where we don't think we should.
• 03:56 We know that 777 works but
• 03:58 what if somebody actually had it formatted as a text value.
• 04:03 The way we could do that is we could put an apostrophe in front of the number.
• 04:07 It's still looks like 777 in the cell,
• 04:10 we still see 777 here aligned to the left and yet it's returning #N/A.
• 04:15 What's going on?
• 04:16 And the reason for this is because these are actually numbers and this text and
• 04:21 those two aren't compatible, so we do have to watch those kind of issues.
• 04:25 Leading or trailing spaces on one side or the other,
• 04:28 the equation can also cost this things where something goes into #N/A state and
• 04:32 you don't think it's should, so those are common problems that happened with
• 04:36 VLOOKUP regardless understanding what's going on is the real key here.
• 04:41 This is an incredibly powerful formula that can actually
• 04:44 really unlock your true understanding and mastery of Excel.
• 04:47 I highly encourage you to spend as much time when you can as to lock it down
• 04:51 because once you get it, every other formula in Excel will just fall before you

Lesson notes are only available for subscribers.