Duplicates can be a pain in data analysis leading to the wrong conclusion. This is why knowing how to lookup unique values is very important.

This article shows 5 ways to tackle duplicate data menace and create a dynamic lookup table with unique values.

The task is to create a dynamic lookup list of unique customers in the table below.

## 1.Using Power Query to create a dynamic list of unique values

Follow the below steps:

• Load the table into the power query; click on the tableâ–ºGo to power queryâ–ºClick From Table

• Once the data is loaded, Right-click Customer columnâ–ºRemove other columnsâ–ºRemove duplicates

• Rename the unique values andÂ  close to load them back to the sheet
• Select you to want to load the list to the existing worksheet and load.

• That’s all…you have a dynamic list of unique values that you can refresh every time yourÂ  data grows

## 2. Using INDEX, MATCH & COUNT To Lookup Unique Values

What if you do not have Power Query installed?

No worries, write below formula and drag it down.

`{=INDEX(Customer_Name,MATCH(0,COUNTIF(\$F\$1:F1,Customer_Name),0))}`

How the formula works:

COUNTIF(\$F\$1:F2, Customer_Name)â–º counts the number of times a customer is occurring in Unique column (column F)

`{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}`

MATCH(0,COUNTIF(\$F\$1:F2,Customer_Name),0)â–º Returns the relative position of the first zero (0) in the values generated by COUNTIF function

Then INDEX function using the position returned by MATCH function to look up the customer in that row.

NB:

• This is an Array formula, so Ctrl+Alt+Enter
• Turn your unique values list into a table to ensure it refreshes every time your data grows.
• If you don’tÂ want to see the errors, wrap the function with IFERROR as shown below

{=IFERROR(INDEX(Customer_Name,MATCH(0,COUNTIF(\$F\$1:F1,Customer_Name),0)),””)}

## 3.Using Pivot Table to Lookup Unique Values

After posting the article on Linkedin,Â Lukasz Komarewicz’sÂ comment on the use of pivot table gave rise to this 3rd method.

Here are the steps;

â–ºInsert Pivot table, Select a location in the ExistingÂ worksheet. Click Ok.

â–º Select the customer name only and close the pop-up

â–ºRename the field, Go to the pivot table design, Grand Totals and turn off grand totals

That’s all, you will have a dynamic list of unique values. All you need is to right-click it and refresh to update

## 4.Using Remove Duplicates to Lookup unique values

This last method is the easiest in looking up unique values. However, it does not create a dynamic list.

Here are the steps:

â–ºCopy and past the customer’s list on a new column.

â–ºGo to DATA, Remove Duplicates and on the pop-up window click OK. Rename the list

Edit 2:

## 5. Using Advanced Filter to Lookup to Unique Values

The second comment on my Linkedin articleÂ byÂ William J CrabtreeÂ gave birth to this fifth method.

However, like the remove duplicate method, this method also does not create a dynamic list–just a static list of unique values.

Here are the steps:

â–ºCopy customers list, then go to the DATA tab and click Advanced.

â–ºOn the advanced filter tab, Select “copy to another location”, then select a location to “copy to”.

â–ºFinally, select Unique records and click OK…That’s All!!!

Download the Worksheet and try it yourself

If I have missed any other method, leave a comment and let me know.

## Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

## You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

### Spread the Good News!

Do me favour, please? Share this with your friends!

### Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.