Home > Microsoft Office > Microsoft Office How To

How to compare two columns in Excel – 6 simple methods

Read this article to find out how you can compare two columns in Excel!
Last Updated on March 6, 2024
A green excel icon on a blue background with the word "compare columns guide" and a colorful symbol in the top left corner.
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

If you want to know how to compare two columns in Excel, you’ve landed on the right page.

Excel is a spreadsheet software program that you can use to deal with massive databases. But it doesn’t only serve as a program to store data, as you can use different formulas to make calculations and even compare data. However, the process can seem tricky for some people, especially if they are new to Excel.

This is where we come in. In this guide, we will go over six different methods that can help you compare two columns in Excel. So, without wasting another second, let’s dive in!

How you can compare two columns in Excel

Scenario on hand: We have a supermarket dataset in Excel.  

What we want to accomplish: Explore how to compare two columns in Excel using the following ways:

  1. Exact match using the IF formula
  2. Case-sensitive differences in columns
  3. Compare two lists in Excel 
  4. Compare two lists using INDEX and MATCH
  5. Compare two lists with ‘Find & Select’
  6. Compare two lists for partial matches

Exact match using the IF formula

You can easily compare two columns in Excel using the IF() formula. This formula compares two values and returns either TRUE or FALSE, depending on whether they match.

For this method, simply type the formula:

IF(cell address 1=cell address two,{“Value if true”},{“Value if false”})

Our dataset has customer names with two contact numbers for each customer. However, some customers have given one contact number twice. To check this, we use this formula:

=IF(D6=E6,”Same”, “Not Same”)

Here’s the result we get for the first cell:

How to compare to columns in Excel

We drag this formula to the rest of the table using the Fill Handle Tool, a small square at the bottom left of the cell. To use it, we press down the cursor and drag it to the rest of the table before releasing it. Excel dynamically changes the formula to compare each row in a similar fashion:

A screenshot of an Excel spreadsheet with a dataset to compare columns of contact numbers, highlighting matches and mismatches with conditional formatting.

Case-sensitive differences in columns

When handling data, you should ensure that your data is in the same format. However, in data entry, people may enter in all caps or keep the first letter capitalized. This trick can come in handy to ensure that your data is uniform. 

Let’s consider two columns with data. Some of these columns have matching data, while others are matching but have case differences.

A screenshot of an excel spreadsheet showing how to compare columns in Excel, with a table titled "dataset to compare two columns," featuring a list of customer numbers with corresponding customer names in two separate columns,

To see if the names are a case-sensitive match, we use the IF(EXACT combination. Here’s the syntax for this formula:

=IF(EXACT(cell address 1, cell address 2), “{Value if matching}”, “{Value if not matching}”)

For our dataset, we use the formula like this:

=IF(EXACT(C6, D6), “Match”, “Unique”)

Here’s the result:

A screenshot of a Microsoft Excel spreadsheet with a dataset to compare columns, indicating whether names from the 'customer name' and 'customer name in excel' columns match or are unique. The comparison results.

Compare two lists in Excel 

The two methods above are for comparing the columns row-by-row. However, there will be very few cases when you find data matching row-by-row. 

Most times, you will have two separate lists and will have to find the duplicate values amongst them. The IF and IF(EXACT) formula wouldn’t work in this case. 

Here’s a simple way to compare two tables or columns using VLOOKUP. 

Simply write the following IF(VLOOKUP formula:

=IF(VLOOKUP({cell address of lookup value},{column array with absolute references},1,FALSE)={cell address of lookup value},”Duplicate”)


Here’s what we get for a table with two customer columns:

How to compare columns in Excel.

This formula takes one value and looks through it in the other list. If it is repeated in the list, the formula returns “Duplicate.” If it doesn’t find a match, it gives an #N/A error. 

If you wish to remove the #N/A error, use IFERROR instead of IF. Here’s how the modified formula looks like:


This returns the name of the person if it finds a duplicate match and “Unique” if it does not find a match:

The image displays a Microsoft Excel spreadsheet with a dataset to compare columns labeled "customer name" and "alice johnson duplicate." An error in a cell is highlighted, indicated by a formula error "#ERROR

Compare two lists using INDEX and MATCH

You can use the VLOOKUP formula to return duplicate values in a third column. You can also do the same using an INDEX and MATCH formula. 

This method is also quite simple. Here’s the syntax it uses:

=INDEX({column array with absolute references}, MATCH({Cell address to lookup}, {column array with absolute references}, 0))

Here’s what the formula we used for our dataset:

=INDEX($B$5:$B$11, MATCH(C5, $B$5:$B$11, 0))

Here’s the result we get:

A screenshot of microsoft excel with a dataset titled "compare columns excel." The worksheet shows two columns of customer names with a third column using a formula to find matches between the first two.

Compare two lists with ‘Conditional Formatting’

Another great way to identify unique and duplicate values in your rows is by using the ‘Conditional Formatting’ option found in the Home tab. 

To use this option, first select both the columns you want to compare. Then, click ‘Conditional Formatting’ > Highlight cell rules > Duplicate Values:

The image shows an open Excel spreadsheet with a column titled "customer name" next to another titled "alice johnson," prepared to compare columns. The "conditional formatting" dropdown menu in the toolbar is

This will open up a window where you can select the highlight color for duplicate values:

A screenshot of Microsoft Excel with an open dialog box titled "duplicate values" over a spreadsheet that highlights duplicate names in a column, illustrating how to compare columns in Excel for duplicate information.

Compare two lists for partial matches

You can even compare two lists for partial matches. We changed the dataset a bit for this demonstration to include the customers’ numbers. 

A screenshot of a Microsoft Excel spreadsheet with a title in cell B2 stating "dataset to compare columns in Excel," containing two sets of columns labeled "customer name" and "customer number.

We need to look up the partial values from column F and fetch the values they correspond with from the original table. 

Here’s the VLOOKUP formula we use for this:


In this formula, we use the wildcards “*”& on either side of the cell address of the lookup value to get a partial match from the dataset. Here’s the result we get using this formula:

An Excel spreadsheet is displayed with a title "compare columns in Excel to identify duplicates". It shows two columns labeled 'customer name' and 'customer number', with a third set of columns highlighting duplicates from

Wrapping up

There will be many times when you have to compare two columns in Excel. You can use formulas to find exact matches in columns. You can also use an IF and EXACT formula to find case-sensitive matches. While there are different ways to compare columns in Excel, as long as you have the appropriate formulas, you can get your work done.

If you want to learn more about Excel, give these guides a read:

Andrew is one of three co-founders of BGFG, the parent company of PC Guide. A keen gamer and PC enthusiast, Andrew dabbles in a bit of writing sometimes - when he gets the chance to!