Excel Compare Columns

Get FREE Advanced Excel Exercises with Solutions!

Here, we will learn Excel compare columns using different processes and formulas such as conditional formatting, IF function, EXACT function, AND function. We will use all possible functions as well as different procedures.

Comparing columns is a very basic yet vital feature. We can get the duplicate and unique values by comparing two columns. Comparing columns creates data cleansing, deletes differences, analyzes the data, and returns the proper result. If you compare columns, then you can identify the matches and sort the data according to the match, making the dataset more readable and more presentable as well.

The overview of this article is shown below. You may follow the whole article to get proper information about excel compare columns.

Overview of Excel compare columns

How to Compare Columns in Excel: 9 Methods

To compare columns in Excel here we will use different procedures and formulas.

Method 1: Comparing Two Columns in the Same Row

In this method, we will use the IF function to compare two columns. here is the overview image of the IF function below. Click on the image to get a proper visualization.

Overview of IF function

  • If the value in column C matches the value in column D, then the formula will return the value as Matched.
=IF(C5=D5,"Matched","Not Matched")

Comparing two columns in Excel in same row

Method 2: Using EXACT Function to Compare Upper/Lower Case Data in Columns

The previous method returns output if the value matches, but sometimes values are not the same when they’re written in a different case. Here we will use the EXACT function to differentiate and compare upper case or lower case data.

  • Now, select cell E5 and enter the below formula.
=IF(EXACT(C5,D5),"Matched","Not Matched")

Comparing columns in Excel using EXACT function

Formula Breakdown


  • This function returns the exact value of the cells.

IF(EXACT(C5,D5),”Matched”,”Not Matched”)

  • Here, if the columns match the value with the same case, then this formula returns the value as “Matched” otherwise “Not Matched”.

Method 3: Comparing Multiple Columns in Same Row

Here we will use the IF and AND functions to compare multiple columns in the same row.

  • Insert the formula in cell F5 and compare the data in multiple columns.
=IF(AND(C5=D5, C5=E5), "Full match", "Not Matched")

Comparing Multiple columns in Excel

Formula Breakdown

AND(C5=D5, C5=E5)

  • The AND function adds the value if there are more than two values.

IF(AND(C5=D5, C5=E5), “Full match”, “Not Matched”)

  • Here, the formula will return the value if all the columns match the value; otherwise “Not Matched”.

Read More: How to Compare Two Columns or Lists in Excel

Method 4: Using Conditional Formatting to Compare Columns with Matches and Mismatches

Here, we will use conditional formatting to compare the matches and then highlight the matches with suitable colors.

  • In the beginning, select range C5:D12 and click on Conditional Formatting from the toolbar.
  • The drop-down option will pop up, and select New Rule from the list.

Applying New Rule

  • Edit Formatting Rule dialog box will pop up. and select Use a formula to determine which cells to format, write down the formula, and click OK.

Applying conditional formatting

  • The final output will be below.

The final output of comparing columns with matches

  • Now, we will compare the mismatches in the columns. All the Procedures will be the same, but the formula.

Applying conditional formatting to compare columns with mismatches

  • Lastly, the final output will be similar to below.

The final output after applying conditional formatting

Read More: Excel Formula to Compare and Return Value from Two Columns

Method 5: Combining IF and COUNTIF Functions to Compare Two Columns

Here, we will combine the IF function and the COUNTIF function to compare two columns. Initially, select cell E5 and enter the formula.

  • Therefore, apply the formula in cell E5 as below.
=IF(COUNTIF($D:$D, $C5)=0, "No match in D", "Match")

Combining fucntions to compare two columns in Excel

Formula Breakdown

COUNTIF($D:$D, $C5)=0

  • This part returns the value as true or false. The COUNTIF function counts the matches.

IF(COUNTIF($D:$D, $C5)=0, “No match in D”, “Match”)

  • This formula returns the value if there is a match in columns C and D; otherwise, it returns the value “No match in D.

Read More: How to Compare Two Lists and Return Differences in Excel

Method 6: Combining INDEX MATCH Functions to Pull Matches from Two Lists

The INDEX function and the MATCH function pull out the matches and return the value of the corresponding lookup value.

  • Enter the formula in cell B15 and execute this process.
=INDEX($D$5:$D$12, MATCH($B$15, $B$5:$B$12, 0))

Combining INDEX MATCH functions to pull matches from two lists

Formula Breakdown


  • The INDEX function returns the result value here.

INDEX($D$5:$D$12, MATCH($B$15, $B$5:$B$12, 0))

  • Here, the lookup value is in cell B15, and if the value matches column B, then this formula will return the corresponding value from column D.

Read More: How to Match Two Columns and Return a Third in Excel

Method 7: Utilizing VLOOKUP Function to Compare Values from Columns

The VLOOKUP function matches the range with the lookup value, and here is the overview image of the VLOOKUP function. Click the image for better visualization.

  • Now, the lookup value in cell B15 matches the lookup range, then the formula returns the corresponding value of the column.

Applying VLOOKUP function

Read More: Comparing Two Columns and Returning Common Values in Excel

Method 8: Combining IF and MATCH Functions to Compare Two Columns

Here we will apply the IF function, the ISERROR function, and the MATCH function to compare two columns.

  • Select cell E5 to execute this process.
=IF(ISERROR(MATCH( $C5,$D:$D,0)), "No match in D", "Match")

Combining IF and MATCH functions to compare two columns in Excel

Formula Breakdown

MATCH( $C5,$D:$D,0)

  • This part searches the value of cell C5 in column D for an exact match.


  • This part returns the value as True if there is any error; otherwise, it is false.

IF(ISERROR(MATCH( $C5,$D:$D,0)), “No match in D”, “Match”)

  • The formula will search the value of cell C5 in column D and returns the value as “Match” if there is any match; otherwise, “No Match in D”

Method 9: Comparing Two Columns in Excel and Counting Matches

In this method, we will use the SUMPRODUCT function to compare the columns and count the matches. Here is the overview image of the SUMPRODUCT function is below. click on the image to get better visualization.

Overview of SUMPRODUCT function

  • This formula will count the matches in the ranges C5:C12 and D5:D12.

Comparing two columns in Excel and counting matches

Read More: How to Compare Text in Two Columns in Excel

How to Compare Rows in Excel: 2 Methods

To compare rows in Excel here, the column will be the same and the rows will be different.

Method 1: Comparing Multiple Rows in Same Columns

In this method, we will compare and highlight the rows in the same columns using Conditional Formatting.

  • This process is similar to before, but the formula has changed. Insert the below formula to execute this process.

Comparing Multiple Rows

  • Here is the final output after completing the process.

Comparing multiple rows output

Method 2: Using the IF Function to Compare Multiple Rows in the Same Column

In this method, we will use the SUM function and the IF function to get the sum of the matches in column D.

  • Select cell B15 and execute the process.
=SUM(IF(D5:$D$12= D5, 1, 0))

Using the IF function to compare multiple rows

Formula Breakdown

IF(D5:$D$12= D5, 1, 0)

  • This part returns the count of matched values in column D.

SUM(IF(D5:$D$12= D5, 1, 0))

  • This formula will return the sum of the matched value counts in column D.

Things to Remember

  • Make sure that the columns you are comparing contain the same type of data. For instance, both columns should contain Text or Numbers.
  • Sometimes case sensitivity is important, so use the EXACT function if the output needs to be case sensitive.

Download Practice Workbook

You may download the workbook for practice.


In this article, we learned how to perform Excel compare columns using different processes and formulas. Here, we also learned how to compare rows in the same column. We covered every possible aspect of this process. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.

Frequently Asked Questions

Q1: How do I compare two Excel lists for matches?

Ans: First, select the lists and go to Conditional Formatting >> Duplicate Values >> OK.

Q2: How can I compare 3 columns in Excel?

Ans: If you want to compare three columns, then use this formula- =IF(AND(C1=C2, C1=C3), “Full match”, “Not Matched”)

Q3: How can I compare columns in different sheets?

Ans: To compare columns in different sheets follow the formula here Sheet1!B5=Sheet2!B5.

Excel Compare Columns: Knowledge Hub

<< Go Back to Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF