×

List Duplicates or Missing Values By Comparing Two Columns In Excel

By Chris Newman •  Updated: 11/20/23 •  3 min read
How to create multiline text in ribbon tooltips with Excel.

Comparing Two Lists With An Excel Formula

Sometimes the real world provides us with situations where we need to compare lists. Whether it's comparing an attendance list for an event or trying to audit that values exist in both sets of data. These sorts of comparison tasks are far too often manual when they don't have to be.

Let's take a look at how you can use a single Excel formula to dynamically curate a list of values that are shared and not shared between two data ranges on your spreadsheet.

Compare Two Lists In Excel to determine shared and not shared values

Formula For Identifying Non-Shared Values Between Ranges

Let's first tackle isolating values between two contact lists that are not shared. Or said another way, the names only show up in one of the two lists.

In this formula solution, we'll use the Dynamic Array Function UNIQUE in combination with VSTACK in order to compile our list of non-shared values.

Compare Two Lists In Excel to determine  not shared values

Excel Formula From This Example:

=UNIQUE(VSTACK(UNIQUE(B4:B18), UNIQUE(D4:D21)), , 1)

How This Formula Determines Shared Values

UNIQUE(B4:B18) and UNIQUE(D4:D21): This removes any duplicates from the list.

VSTACK(B4:B18, D4:D21): The VSTACK function vertically stacks two arrays/ranges. In this case, it stacks the range of names from B4:B18 on top of the range from D4:D21. This results in a single-column array that contains all names from both ranges, one after the other.

UNIQUE(..., , 1): The UNIQUE function then takes this stacked list of names and returns a list of unique names. The third argument of the UNIQUE function, which is set to 1 (or TRUE), specifies that it should return unique rows. Since the data is already in a single column due to VSTACK, this argument effectively removes duplicate names from the combined list, regardless of their original position in the two input ranges.

The result of this formula is a single-column array of unique names from both the B4:B18 and D4:D21 ranges, eliminating any duplicates between or within these two lists.


Formula For Identifying Shared Values Between Ranges

Now let's take the opposite approach and determine contacts in the example data that exist in both lists. In this formula solution, we'll use the Dynamic Array Function FILTER along with ISNUMBER and MATCH to assist with the logical testing.

Compare Two Lists In Excel to determine shared values across both lists

Excel Formula From This Example:

=UNIQUE(FILTER(B4:B18, ISNUMBER(MATCH(B4:B18, D4:D21, 0))))

How This Formula Determines Shared Values

MATCH(B4:B18, D4:D21, 0) looks for each value in range B4:B18 within the range D4:D21 and returns their position in D4:D21 if found. If not found, MATCH returns an error.

ISNUMBER(…) checks whether the MATCH function's output is a number. This will be TRUE for values found in both columns and FALSE for those that aren't.

FILTER(B4:B18, …) then filters range B4:B18's values, only returning those for which the second parameter (the ISNUMBER(MATCH(…)) part) is TRUE.

UNIQUE(...) removes any duplicate values that may be within the list you are filtering on


Download The Excel File I Used

Download Example Excel File

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can write formulas that utilize Dynamic Array functions to compare two ranges and isolate shared and unshared values. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.