How to Compare Two Lists in Excel

When analyzing data you often have to compare two lists to see if

  • They both contain the same data
  • You’re missing data from one of the lists
  • There are any duplicates or misspellings
  • etc..

If you’re looking to compare two lists for equality, you can use simple comparison operators.

compare lists excel

By turning column C into a helper column, we can compare the lists in A and B by simply typing

  1. =A2=B2

and auto-filling that formula down the column. As you can see this works for text, numbers, and dates the same way.

If you need to check to see if certain values exist or not in the other list, I’d suggest using ISNUMBER and MATCH together to find your answers.

Let’s separate the two columns for a second and add some additional values to List #2:

compare lists excel_2

We’re going to do a check to see which values in List 2 are in List 1.

We’ll create a helper column in Column D and first use the MATCH function to return the ROW that a match exists on, if any.

compare lists excel_3

  1. =MATCH(C2,A:A,0)

Excel searches for each of the values in column C inside of column A. The numbers returned are the ROW in column A where a match was found, while #N/A simply means there was no match found.

By wrapping this function in the ISNUMBER function, we can simplify this to only return TRUE (if a row number is found) or FALSE (error, no match).

compare lists excel_4


We can now see exactly which values exist in both List 1 and List 2.

What is a Pivot Table?

A pivot table is a feature of Microsoft Excel that allows a user to quickly summarize and analyze a large set of data.

Everything that a pivot table does can be done by hand or by using other Excel formulas. However, pivot tables often speed up the process and allow the person receiving the report to quickly identify what’s important in the data.

Check out our more in-depth series on Pivot Tables:

  1. Pivot Tables – Part 1 – Setup and Features
  2. Pivot Tables – Part 2 – Conditional Formatting

How to use INDEX and MATCH together

I’d probably list INDEX-MATCH in the top 5 things to know in excel if you want to add the skill to your resume and not seem like you’re “fudging the details”.

To use them together, one must first understand how they operate independent of each other.

  1. The INDEX() Formula – Returns a value from an array given the row and column number.
  2. The MATCH() Formula – Returns the row number of a match to a given argument.