How to Compare Two Columns in Excel? (4 Easy Methods)

MS Excel spreadsheets are useful for data storage, manipulation, and decision-making. Since such spreadsheets are huge and often multiple spreadsheets are linked to one another. In that case, manually comparing columns is a tedious task. It might take hours or days to find the missing data. Knowing certain functions that facilitate tasks and optimise your productivity is necessary. In this blog, we will cover how to compare two columns in Excel.

Depending on your specific goal, you can use various methods to compare columns with rows with the same data in Excel. Here's how you can do it:

Table of Content

• “Equal” Function
• “EXACT” Function
• “IF” Function
• “VLOOKUP”
• What is the Purpose of Comparing Two Columns In Excel?

Compare Two Columns In Excel Using the “Equal” Function

The Equal function compares values row by row to identify exact matches. Here are the steps involved:

Step 1 - Identify the two columns you want to compare. In this case, we are tallying two tables

Step 2 - Insert a new column next to the columns you want to compare. This column will display the comparison results.

Step 3 - Click on the first cell of the new column (e.g., in our case, it is Column C; click on C3).

Step 4 - Enter the EQUAL formula to compare the values in the corresponding cells in the two tables with data in Column B. We will use the below formula -

=B3=B16

Step 5 - Press Enter to apply the formula to the cell.

Step 6 - Drag the fill handle down the column to apply the formula to all rows you want to compare.

The new column will display TRUE if the values in the corresponding cells in Column B are identical and FALSE if they differ.

Compare Two Columns In Excel Using the “EXACT” Function

You can compare two columns using the EXACT function to determine if the values in corresponding cells are exactly the same. 

Step 1 - Ensure your data is organised with the two columns you want to compare. Let's say Column C contains the values you want to search for in Column G.

Step 2 - Insert a new column where you want the comparison results to appear.  

Step 3 - In the first cell of the new column, enter the following formula:

=EXACT(C3,G3)

This formula searches for the value in cell C3 in the entire Column G. If it finds a match, it returns "True"; otherwise, it returns "False".

Step 4 - Drag down the fill handle to copy the formula to all cells in the new column corresponding to the data in Column C.

The new column will now display "True" for values in Column C found in Column G and "False" for those not found.

How to Compare Two Columns In Excel Using the “IF” Function?

The IF function in Excel allows you to perform conditional checks and display specific outputs based on whether the values in corresponding cells match or not. Here are the steps involved:

Step 1 - Determine which two columns you want to compare. In our example, we want to compare Column C and Column G.

Step 2 - Insert a new column next to the columns you want to compare. This column will display the comparison results.

Step 3 - Click on the first cell of the new column (e.g., if it's Column I, click on I3).

Step 4 - Enter the IF formula to compare the values in the corresponding cells in Columns C and G.

=IF(C3=G3, "Match", "No Match")

This formula checks if the value in cell C3 equals that in cell G3. If they match, it returns "Match"; otherwise, it returns "No Match".

Step 5 - Apply the Formula and press Enter to apply the formula to the cell.

Step 6 - Drag the fill handle down the column to apply the formula to all rows you want to compare.

The new column will display "Match" if the values in the corresponding cells in Columns C and G are identical and "No Match" if they differ.

Compare Two Columns In Excel Using “VLOOKUP”

You can compare two columns in Excel using the VLOOKUP function to check if the values in one column exist in the other column. Here's the process to do it:

Step 1 - Ensure your data is organised with the two columns you want to compare. Let's say Column C contains the values you want to search for in Column G.

Step 2 - Insert a new column where you want the comparison results to appear.  

Step 3 - In the first cell of the new column, enter the following formula:

=IF(ISNUMBER(VLOOKUP(C3, G:G, 1, FALSE)), "Match", "No Match")

This formula searches for the value in cell C3 in the entire Column G. If it finds a match, it returns "Match"; otherwise, it returns "No Match".

Step 4 - Drag down the fill handle to copy the formula to all cells in the new column corresponding to the data in Column C.

The new column will now display "Match" for values in Column C found in Column G and "No Match" for those not found.

What is the Purpose of Comparing Two Columns In Excel?

Comparing two columns in Excel serves several purposes:

1. Identifying Common Information: When you have data in two columns, comparing them helps you identify common points between the two data sets. It comes in handy for finding duplicates, matching records, or consolidating information.
2. Finding Differences: Besides finding similarities, comparing two columns also helps identify differences. It allows you to pinpoint inconsistencies or missing information.
3. Data Validation: Comparing columns can be part of a data validation process. You can check if the data in one column matches the expected values in another, ensuring data integrity and accuracy.
4. Data Cleaning and Formatting: Comparing columns can help in data cleaning tasks, such as standardising formats, correcting errors, or removing redundant information.
5. Analysing Data Relationships: Comparing columns can reveal relationships between different datasets. For example, you might compare sales data with customer demographics to analyse buying patterns or compare employee performance metrics with training records to assess their performance.

Conclusion

Comparing two columns in Excel can help you identify differences, duplicates, or matching data within a dataset. You can quickly highlight or isolate similarities and discrepancies using the above discussed methods. Mastering these techniques can simplify data analysis, making it easier to spot errors, ensure consistency, or align information across different datasets.