How to Compare Two Columns in Excel: Easy Guide

Become an Business Analysis Expert

With Our Unique Program In Collaboration With IBMExplore Course

What Do You Mean by Comparing Columns in Excel?  

Comparing columns in Excel is a simple act of checking each cell in Excel against the target cells to identify a match. If the match is missing, then a relevant message is displayed.

With the basics discussed, you can move ahead with the practical part.

How to Compare Two Columns in Excel?

Here are several effective methods to compare two columns in Excel:

1. Conditional Formatting in Excel
2. Using the Equals Operator
3. Using VLOOKUP Function
4. Using the IF Formula
5. Using the EXACT Formula

Let’s start with one of the easiest ways to compare two columns in Excel:

1. Conditional Formatting in Excel

Conditional Formatting in Excel is one of the simplest ways to compare columns in Excel. You can execute it in simple steps, as shown below:

Step #1: Select all cells in the spreadsheet

Step #2: Conditional Formatting

Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in the Excel Option.

Step #3: A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.

A. Duplicate Values

When using Conditional Formatting to highlight duplicate values between two columns, Excel allows you to quickly identify and differentiate matching data. This is especially useful when you're comparing two sets of information, such as lists of customers or product IDs, and you want to spot any duplicates.

B. Unique Values

Once you’ve mastered the first method of column comparison, the next step is to implement the equals operator for a more precise comparison.

2. Using Equals Operator

Another simple way to compare columns in Excel is by using the equals operator. You can execute it with the following steps.

Step #1: Create a new result column and add the formula for individual cell comparison, as shown below.

Step #2: Excel will deliver the result as FALSE for every unsuccessful comparison and TRUE for every successful comparison, as shown below.

Step #3: You can make minor tweaks to the formula to deliver customized messages using the "IF" clause with custom messages for "TRUE" and "FALSE" values, as shown below.

The final result will be displayed as follows.

Next up, let us learn to use the VLOOKUP Function to compare columns in Excel.

3. Using VLOOKUp Function

To compare two columns in Excel, you can use the VLOOKUP formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Follow the detailed steps mentioned below to use the VLOOKUP Function to compare individual cells.

Step #1: Create a new result column and add the VLOOKUP Formula to compare individual cells, as shown below.

Step #2: You can drag the formula to all the cells to obtain the desired result, as shown below.

Step #3: You can see traces of failed comparisons displayed as errors. You can modify your formula using the "IFERROR" clause to avoid such errors, as shown below.

Step #4: Now, you can copy-paste or drag the modified formula to all the cells to get the final error-free result, as shown below.

Step #5: Unlike the sample data, the real-world scenarios might vary a little. For example, the comparison between the two individual cells might end up "FALSE" even if both cells represent the same data. Now, take a better look at it with the help of the image below.

The data in columns 1 and 3 are the same. But, there is an additional extension to a few cells.

Example: Ford India in the first cell of column 1 and Ford in the first cell of column 3.

Step #6: The standard VLOOKUP might end up delivering "FASLE" as a result. You can use wildcards as a minor tweak to the data to avoid this, as shown below.

Step #7: Now, drag the modified formula cell to all the cells, and the resultant data will be shown as follows.

With that, you can conclude this tutorial on Compare columns in Excel. 

4. Compare 2 Columns Using the IF Formula

The IF formula is used to compare 2 columns in Excel when you want to display a desired result for a similarity or a difference.

IF Formula: =IF(A2=B2,”Match”,” ”)

Consider the above example if you want the result as follows: 

• “Different car brands” if the name of the brands do not match
• “Same car brands” if the name of the brands match

Using the IF formula, we will compare two columns in Excel, columns A and B.

We will be using the formula: “=IF(A2=B2, “Same car brands,” “Different car brands”).” 

If the values match, this formula will return “Same car brands” for every “true” value. Likewise, return “Different car brands” for every “false” value when the values do not match.

The result is shown in column D in the image below.

5. Compare Using the EXACT Formula

To use the EXACT formula to compare two columns in Excel, we can use the following formula for the above example:

“=EXACT(A2, B2)”

If the value in one column is exactly the same as the other column, the result will be displayed as “true,” and if the values are not equal, the result will be “false.”

One should keep in mind that the EXACT formula is case-sensitive. If we write “Honda” in two different cases and apply the formula “=EXACT(A12, B12),” we will get the result “false,” whereas if the case is the same in both cells, then the result will be “true.”

Looking for a career boost? This Data Analyts Masters Program will set you apart in the competitive industry. 🎯

Which Comparison Method to Use in Each Scenario

We have learned the methods to compare two columns in Excel. Here is a description to help you choose the method for certain scenarios:

Scenario 1: Comparing Two Columns in Excel Row-by-Row

To compare two columns in Excel row-by-row, use the following formulas:

• =IF(A2 = B2, “match”, “ ”)
• =IF(A2<>B2, “no match”, “ ”)
• =IF(A2 = B2, “match”, “no match”)

If you need the results to be case-sensitive, then use the following formulas:

• =IF(EXACT(A2, B2), "Match", " ")
• =IF(EXACT(A2, B2), "Match," "No match")

Scenario 2: Comparing Multiple Columns for Row Matches

If you need to compare and find the differences and similarities between more than two columns, then use the following formulas: 

• =IF(AND(A2=B2, A2=C2), "Complete match", " ")
• =IF(COUNTIF($A2:$E2, $A2)=4, "Complete match," "), where 4 is the number of columns you are comparing

If you want to compare columns with any two or more cells with the same values in the same row, then you might use the following formulas:

• =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")
• =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0,"Unique","Match")

Scenario 3: Compare Two Columns for Matches and Differences

To compare two datasets, to find the unique values present in column A and not in column B one can use any of the formulas for finding the match and differences: 

• =IF(COUNTIF($B:$B, $A2)=0, "Not present in B", "")
• =IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"No present in B","")

You can use a single formula to get the result for matches and unique values: 

• =IF(COUNTIF($B:$B, $A2)=0, "No Present in B", "Present in B")

Scenario 4: Compare Two Lists and Pull Matching Data

To compare two lists and find the matching data, you can use the VLOOKUP function. You can also use the INDEX MATCH formula. You can use the following formulas for this scenario: 

• =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
• =INDEX($B$2:$B$6, MATCH($D2, $A$2:$A$6, 0))
• =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

A2, B2 and D2 are the first cells of three columns. 2 is the number of columns compared.

Scenario 5: Highlight Row Matches and Differences

You can create a conditional formatting formula that can highlight the rows that include identical values in all the columns. You can use the following formula for the desired result:

=AND($A2=$B2, $A2=$C2)

or

=COUNTIF($A2:$C2, $A2)=3

3 is the number of columns and A2, B2 and C2 are the top-most cells, to compare.

You can also use the following steps to find and highlight the matches and differences in Excel: 

1. Select the columns with the dataset you want to compare. 
2. Go to the editing group section on the Home tab, click the “Find and Select” drop-down, and choose “Go To Special.” Select Row Differences and click OK.

3. The cells having different values than the cells compared in each row will be colored. To change the color click the Fill Color icon on and choose the color of your choice.

Data Analyst with the advanced skills are in high demand and this is your chance to become one of them! ✨🎯

Conclusion

Comparing columns in Excel may seem like a basic task, but it’s an important skill that ensures your data is accurate, consistent, and ready for analysis. From handling sales numbers to managing project data, knowing how to compare two columns in Excel helps streamline your workflow and reduce manual effort.

To enhance your data analytics skills and boost your career, explore Simplilearn’s Business Analyst certification training course. This program covers everything from analyzing data to presenting insights clearly, ideal for anyone looking to grow as a business analyst.

Advance your knowledge through our popular online courses, available across multiple categories.

• Analytics Courses
• Business Analytics Courses
• Business Marketing Courses
• Data Analytics Courses

Become a Business Analysis Expert In Just 6 Months

With Our Best-in-class Post Graduate ProgramExplore Now

FAQs

1. How to compare two columns in Excel?

One popular method for comparing two columns in Excel is to follow these steps: select both columns of data → go to the Home tab → click on Find & Select → choose Go To Special → select Row Differences → click OK.

2. Is it possible to compare two columns in Excel using the Index-Match function?

Yes, you can compare two columns in Excel using the Index-Match function by creating the required formula for the data required. 

3. How to compare multiple columns in Excel?

To compare multiple columns in Excel, you can use the conditional formatting option on the Home and format the setting to “duplicates” or “uniques”, and choose the desired color to highlight the values to compare multiple columns.

4. How do you compare two lists in Excel for matches?

You can compare two lists in Excel using IF function, MATCH function or highlighting row differences.

5. How can I compare columns and highlight the first occurrence of a mismatch?

Use Conditional Formatting with a formula like =A1<>B1 to highlight cells where the values differ.

6. How do I compare columns for duplicates only?

Use the formula =COUNTIF(B:B, A1)>0 to find duplicates between columns A and B.

7. Can I compare columns and count the number of matches or differences?

Yes, use formulas like =SUMPRODUCT(--(A1:A10=B1:B10)) to count matches or =COUNTIF(A1:A10, "<>B1:B10") for differences.