- HD
- 720p
- 540p
- 360p

- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x

We hope you enjoyed this lesson.

Cool lesson, huh? Share it with your friends

## About this lesson

## Exercise files

Download this lesson’s related exercise files.

Lookup Information with Duplicate Lookup Values10.6 KB Lookup Information with Duplicate Lookup Values - Solution

11.3 KB Lookup Information with Duplicate Lookup Values

43.2 KB Lookup Information with Duplicate Lookup Values - Solution

103.3 KB Lookup Information with Duplicate Lookup Values

11.7 KB Lookup Information with Duplicate Lookup Values - Completed

13.2 KB

## 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.

Lesson notes are only available for subscribers.