Lookup Information with Duplicate Lookup Values

Focus video player for keyboard shortcuts
Auto
  • HD
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Excel for Business Analysts course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

Quick reference

Lookup Information with Duplicate Lookup Values

Use VLOOKUP to lookup information in a table when the lookup value is duplicated. 

When to use

We would use this VLOOKUP method whenever duplicate lookup values exist in the table. 

Instructions

VLOOKUP is one of the most popular functions in Excel for looking up data in a table using a lookup value and returning a value from another column. 

However, issues can occur if the lookup value exists more than once in the table. 

The Problem

In the screenshot below, we are using VLOOKUP to search for the employee name in cell H7 (John Smith). We are looking for it in the table on the left and we want to return column 3 (Job Rating). 

However, if we look at the table, there are three occurrences of 'John Smith'. Each 'John Smith' has a different job title with a different job rating. 

The result of the VLOOKUP formula is incorrect. It's incorrect because by default, VLOOKUP will start searching for 'John Smith' from the top down. As soon as it finds a match, it returns the value from column 3. 

Our result is a job rating of 4 but this relates to the first 'John Smith' in the table. We need to return the job rating for 'John Smith, HR Admin' which is the second occurrence in the table. 

We can do this by creating a composite lookup value. 

Create a Composite Lookup Value

A composite lookup value is simply a value that combines more than one piece of information. Composite lookup values essentially create a unique value for each row of the table. We can then use the unique value to perform the VLOOKUP. 

For example, we can combine the employee name with the job title to get a unique identifier for each row in the table. 

  • Select the first column in the table. 
  • Press CTRL+SHIFT+(+) or CTRL+(+) (depending on your keyboard) to insert a new column. 
  • Name the column 'Helper'. 
  • Use & to join together the employee name and job title. 

  • Double-click the small black cross to copy down. 

We now have a composite lookup value that is unique for each row in the table. 

Create a Composite Lookup Value VLOOKUP

We can use the composite lookup value to perform our VLOOKUP calculation. 

In the screenshot below, when we define the lookup value in the formula we simply join the employee name with the job title. Remember, we added a new column to the table so the return column index number is now 4 instead of 3. 

VLOOKUP is now able to find the correct value in the table. 

If we need to hide the 'Helper' column from view we can simply right-click on the column and choose Hide from the menu. 

Hints & tips

  • If the data isn't in an Excel table, remember to press F4 to lock the cell references if we are copying the formula down. 
Login to download
  • 00:04 Maybe in my role as a business analyst,
  • 00:07 I've been tasked with a project to analyze employee performance.
  • 00:11 And in this particular case, maybe I need to pull out of a table
  • 00:16 of data the job rating of a specific group of employees.
  • 00:20 And that job rating might be based on something like a review to give them
  • 00:25 an overall score.
  • 00:26 Now the score that we're using for our employees is a job rating of 1 to 5,
  • 00:31 with 1 being the best, and 5 being the worst.
  • 00:34 And what I need to do here is I need to extract the job rating for these specific
  • 00:40 employees from the information that we have in the table on the left.
  • 00:45 Now we can do this using VLOOKUP.
  • 00:47 But what you'll soon find out is that we can't do it in the normal way, and
  • 00:52 the reason why will become clear in a moment.
  • 00:55 So let's start out by doing just a regular VLOOKUP,
  • 00:58 because that's really going to highlight what the issue is here.
  • 01:02 So what I'm going to do is in the job rating, I'm going to type in =VLOOKUP.
  • 01:07 Now the VLOOKUP formula has four different arguments, with the last one range lookup
  • 01:12 being optional because it's in those square brackets.
  • 01:15 The first argument that we have is the lookup value.
  • 01:18 So we need to tell Excel what we're looking up in the table on the left.
  • 01:22 Now, I'm going to look up people by their name.
  • 01:25 So my lookup value for
  • 01:26 the first one here is going to be cell H7 that contains the name John Smith.
  • 01:31 I then need to tell Excel where I'm looking for that information.
  • 01:35 So what is my table array?
  • 01:38 Now, when you're selecting your table array, you need to make sure that you
  • 01:42 include in your selection the column that contains the lookup values.
  • 01:46 Of the employee name column in this instance, and
  • 01:49 also the column that contains the information that you want to return.
  • 01:53 So we want to return the job rating, so
  • 01:55 I'm basically going to select this entire table.
  • 01:59 So let's select the first row,
  • 02:01 Ctrl+Shift+Down Arrow to select everything, that is my table array.
  • 02:06 Now, because we are going to drag this formula down,
  • 02:09 I don't want those table cell references to move.
  • 02:12 So I need to press the F4 key to lock those cells in place.
  • 02:17 The third argument is the column index number.
  • 02:21 And this is basically the column in that table that contains the information that
  • 02:25 we want to return.
  • 02:27 So we want to return the job rating.
  • 02:29 Now, one thing you need to remember about VLOOKUP,
  • 02:33 and this is a very important point, is that when you're defining
  • 02:37 the column index number, VLOOKUP numbers columns from left to right.
  • 02:42 So to Excel, the Employee Name column is column number 1,
  • 02:46 the Job Title column is column number 2, and
  • 02:50 the job rating column is column number 3.
  • 02:53 So we need to provide the numeric number that relates to the column that we want to
  • 02:58 return.
  • 02:58 So that's the job rating column, which is column number three.
  • 03:02 That's our column index number.
  • 03:04 Now, the last argument here is optional.
  • 03:07 So what exactly does that mean?
  • 03:09 We can choose true, or we can choose false.
  • 03:12 Well true will do an approximate match, whereas false will do an exact match.
  • 03:17 So basically, do I want to exactly match the name John Smith in the table?
  • 03:22 Well, yes, I do.
  • 03:24 So I can put false on the end here.
  • 03:26 So that is a basic VLOOKUP formula.
  • 03:28 Let's hit Enter.
  • 03:29 And if I double click to copy this down, so far it looks like it's working well,
  • 03:35 but let's dive in a little bit deeper, because not all is quite as it seems.
  • 03:40 So at the top here we have John Smith, and his job title is HR Admin.
  • 03:45 Now if I go over to my table and look at where I have John Smith,
  • 03:48 he happens to be the first record in this list.
  • 03:51 But he has a job title of Sales Manager.
  • 03:54 So something's amiss here.
  • 03:56 If I look a bit further down my table, look at that,
  • 04:01 I have John Smith, HR Admin, job rating 3.
  • 04:05 And if I look even further down the table, I have another John Smith who's
  • 04:09 a marketing assistant with a job rating of 5.
  • 04:12 John Smith is a very common name it seems, so
  • 04:15 it looks to me like my VLOOKUP is actually returning the wrong result from the table.
  • 04:21 I'm looking for John Smith in HR.
  • 04:23 It's returned John Smith, Sales Manager, and given me a job rating of 4.
  • 04:29 Now, why is it doing that?
  • 04:30 Well, it's because VLOOKUP by default will look from top to bottom
  • 04:35 through your results for that lookup value.
  • 04:38 So my lookup value is John Smith.
  • 04:40 It starts at the top of the table.
  • 04:42 As soon as it finds the first occurrence of John Smith,
  • 04:46 it returns the job rating result.
  • 04:48 So in this case, it's returning 4, but that's not correct.
  • 04:52 We actually wanted this one just here.
  • 04:55 So how can we do a VLOOKUP when we have duplicate entries for our lookup value?
  • 05:01 Well, there are numerous different methods that you can use to do this.
  • 05:04 I'm going to show you my favorite one, and
  • 05:07 that is to create a composite lookup value.
  • 05:10 Now, what do I mean by that?
  • 05:12 What I'm essentially going to do is I'm going to create a unique value for
  • 05:17 each of the records and then I'm going to use that unique value in my VLOOKUP.
  • 05:22 Now, a quick way of doing this is I can look at my data and I can say okay,
  • 05:27 even though some of these employee names are repeated, the job title is different.
  • 05:33 So for each occurrence of John Smith, whilst the name is the same,
  • 05:36 the job title is different.
  • 05:38 Here we have John Smith Sales Manager, here we have John Smith HR Admin, and
  • 05:43 further down here, we have John Smith Marketing Assistant.
  • 05:47 So what I'm going to do is I'm going to add what I like to call a helper column
  • 05:51 onto the beginning here, and I'm just going to format it,
  • 05:55 because it looks a lot neater.
  • 05:57 And I'm basically going to combine the employee name with the job title
  • 06:02 to give me a unique reference point in this table.
  • 06:06 Now we can do this by using ampersands.
  • 06:08 So let's select Equals.
  • 06:11 I'm going to select the first piece of text that I want, and then I'm
  • 06:16 going to put an ampersand, which basically means join with this cell just here.
  • 06:21 I'm linking these two cells together, Ctrl+Enter to stay in the same cell,
  • 06:26 and you can see it gives me John Smith, Sales Manager.
  • 06:30 And if I copy this down, I'm going to get exactly that same format for
  • 06:34 everybody in this list.
  • 06:36 So now if we take a look we have John Smith, Sales Manager.
  • 06:39 When we get to the next John Smith, that is different from the first one,
  • 06:43 because it says John Smith, HR Admin.
  • 06:45 So these are now all completely unique.
  • 06:49 So we can use these as our lookup value to return the correct result.
  • 06:54 So let's do our VLOOKUP again.
  • 06:56 Our lookup value this time is going to be John Smith,
  • 07:00 ampersand link together, and then the job title.
  • 07:04 Now the rest of this formula is exactly the same.
  • 07:07 So we're going to select our table array.
  • 07:09 Now this time we need to include the helper column as well.
  • 07:13 So Ctrl+Shift+Down Arrow, let's work up in the formula bar,
  • 07:18 what's the index number that we want to return?
  • 07:22 Well, we have an extra column now because we added a helper column, so now,
  • 07:26 the index column that we want to return is column number 4, as opposed to 3.
  • 07:31 And then we just want to do an exact match of that composite lookup value.
  • 07:37 Close the bracket, hit Enter, and let's double click and see if this is working.
  • 07:43 John Smith, HR Admin, job rating 3.
  • 07:47 If we look down, John Smith, HR Admin, job rating 3.
  • 07:52 And of course then, what we can do is simply right click and
  • 07:56 hide this helper column so that no one is any the wiser.
  • 08:00 So that is how you can do a VLOOKUP when
  • 08:05 the lookup value is repeated in the table.

Lesson notes are only available for subscribers.

Gift this course