5 Ways to Lookup Unique Values in Excel

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)),””)}

Edit 1:

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.