Locked lesson.

Upgrade

About this lesson

Learn to use VLOOKUP to find an exact match and return it or the corresponding value from another column.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

VLOOKUP - Exact.xlsx
9.4 KB VLOOKUP – Exact - Completed.xlsx
9.8 KB VLOOKUP with Exact Match - Extra Practice.xlsx
13.1 KB

Quick reference

Topic

Using VLOOKUP to find an exact match in a data table and return it or the corresponding value from another column.

When to use

Using VLOOKUP with an exact match is ideal to look up product numbers, customer data or other data where there is a unique key to find in a data table.

Instructions

VLOOKUP’s parameters (in English)

  • =VLOOKUP([what should it look for],[where is the table to look in],[which column should it return a value from],[guess if it can’t find a match])

Key points with VLOOKUP and exact matches

  • The final parameter for VLOOKUP with an exact match must be set to FALSE.
  • The first column of your data does NOT need to be sorted in ascending order. (VLOOKUP will keep looking until it finds a match or figures out there is no match.)
  • The column to return is the column of the table, not the column of the spreadsheet (if your VLOOKUP table starts in column B, then 1 refers to column B, as it is the first column in the table.)
  • VLOOKUP exact matches are NOT case sensitive. (Looking up dog in a table will return Dog.)
  • We can nest formulae into the “what to look for” section of the VLOOKUP formula.
  • If VLOOKUP cannot find a match it will return #N/A.

Common VLOOKUP #N/A causes

  • There is a space at the end of the value being looked up or the value in the table.
  • One side of the lookup value or table is text, while the other is numbers.

Syntax

  • =VLOOKUP(5000,A1:B10,2,FALSE)
  • Will return the value from the second column of the table where the record in column A is equal to 5000.
  • If the exact value of 5000 is not in the first column of the table, it will return #N/A.
Login to download
  • 00:04 In this video we're going to look at using VLOOKUP with an exact
  • 00:07 match. This particular version of the function is really
  • 00:11 useful and it really shines when you are trying to look up something from a product list or
  • 00:15 some kind of a different list where you can only come back with one result.
  • 00:19 The big keys that really make VLOOKUP sing with an exact match are where there's only one record that could potentially come back.
  • 00:27 If that's not the case then it's going to be a little bit difficult and your results
  • 00:32 are going to be a little bit unpredictable because you can't actually tell where VLOOKUP is going to start
  • 00:37 or where it's going to finish.
  • 00:39 Now the neat thing about VLOOKUP with an exact match is though unlike the vlookup with approximate match your data does not need to
  • 00:46 be sorted. So in this particular case if we've got a list of customer numbers
  • 00:50 and the treatments that they need and what the cost is we can actually go and
  • 00:54 look up even though these numbers are all out of order here we can still look up the actual treatment that the customer needs.
  • 01:01 The way that we'd do that is we go into our VLOOKUP function we say =VLOOKUP
  • 01:05 and it's going to ask us a few different things. The first thing it says is what would you like
  • 01:10 to look up? That's what this vernacular means, it's what, where, where's my
  • 01:14 result and would you like me to guess. So the first thing we're going to say is I'm going to look up the value that I'm going to put in cell B3
  • 01:22 and then it says where would you like to look it up? That's what table array means so we're going to look that
  • 01:27 up inside our huge table from A6 to C18.
  • 01:32 And then it says which column would you like to actually return once you
  • 01:37 find that match in the first column. Now this is not column
  • 01:42 A, B or C if this table started over here column one would be the first table or first column in that table.
  • 01:48 So in this particular case I'm going to pullback the treatment needed which is column number two.
  • 01:52 And then the most important thing with an exact match is to come back
  • 01:56 and actually specify that it's false. Do not guess. Return an error if you can't find it.
  • 02:02 When we close that bracket and we hit Enter right now we get #N/A.
  • 02:07 This is not a bad thing what this is telling us is that it can't
  • 02:10 find a match for the item that's in cell B3 because
  • 02:14 that's where I set it up to look. So let's see if we can actually pick up one of these numbers.
  • 02:18 Let's try 10456493
  • 02:22 and when we hit Enter it comes back and tells us that the treatment that this person needs is
  • 02:26 drug addiction and here it is right here in the table.
  • 02:30 Great.
  • 02:32 Now what would happen if I added an extra number to the end of that?
  • 02:36 It comes back and it says #N/A. So what this is actually signifying is that it can't find this value in the table.
  • 02:43 And this is where as I say this works really well for things like product lists because
  • 02:47 you don't want it to try and guess and find the right thing. Can you imagine if
  • 02:52 you are actually looking up a treatment that somebody needed here and you
  • 02:56 didn't find what they're looking for or the records not there and you told them they
  • 02:59 needed a kidney transplant that will be bad news. Especially
  • 03:03 when they found out it was $265,000 that they don't have. So we don't want to make errors in that and that's why
  • 03:08 this false making sure you get an exact match is good and if it returns an error
  • 03:13 that's ok because we now know that it's not there.
  • 03:17 Something else that's important to realize, I'm just going to back this off one here and say Enter.
  • 03:21 So we now know the drug addiction works here.
  • 03:25 If I were to go and put an apostrophe in front of this, this will force this cell to
  • 03:29 text instead of being a value and when I hit Enter on this it now comes back
  • 03:33 with a #N/A error. This confuses a lot of people because when you look at this value
  • 03:39 you can look down this list and you can say well here it is it's left aligned, it's left aligned, everything is the same.
  • 03:44 If you ever see that happen where you're getting #N/A and your number or your value is plainly in the list there's a few things that could be
  • 03:52 driving that. One there could be a space after this particular value here in the cell one or two
  • 03:57 maybe it has space beforehand and maybe one's text on one side and one's number on the others.
  • 04:02 So you have to watch for those things and make sure that the data type is consistent in the
  • 04:06 look up column as well as the data that you're looking up. That's really important.
  • 04:10 For me I'm going to change this back to a value now so I can guarantee that it's going to work correctly.
  • 04:16 So that's how we work with VLOOKUP with an exact match.

Lesson notes are only available for subscribers.

Gift this course