How To Compare Two Columns In Excel

H

How to compare two columns in excel? or can we even do that? know all about it in this thorough article.

While we work on Excel, one of the most common things that we do is that compare two columns. Microsoft Excel gives us the option to compare and match data. Now let us understand how this is done. 

How to Compare Two Columns in Excel

How to Compare Two Columns in Excel Row-by-Row

Upon performing data analysis in Excel, one of the tasks that are often performed is comparing data in each individual row and this can be done by the IF function. Here is how it is done.  

For example, compare two columns for differences or matches in the same row

To compare two columns in Excel row-by-row, write the IF formula with the help of which you compare the first two cells. 

Enter the formula in some other column of the same row, and copy it down to other cells by dragging the fill handle. As you do this, the cursor changes to the plus sign:

Copy the formula down to other cells to compare two columns in Excel

Formula for Matches

To find cells within the same row having the same content, A2 and B2 in this example, the formula is as follows:

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

Formula for Differences

To find cells in the same row with different values, replace the equals sign with the non-equality sign (<>):

=IF(A2<>B2,”No match”,””)

Matches and Differences

To find both matches and differences with a single formula:

=IF(A2=B2,”Match”,”No match”)

Or

=IF(A2<>B2,”No match”,”Match”)

The result would be something like the below:

A formula to compare 2 columns for matches and differences in each row

This formula manages dates, numbers, dates, text strings and times equally well.

Also Read
How to remove space in Excel
How to add drop down in Excel
How to unhide columns in Excel

How to Compare 2 Columns in Excel Using Lookup Function

Here is how you can make use of the LookUp function to do the comparison:

Create a new result column and add the VLookUp Formula to compare individual cells.

Compare_columns_in_Excel_10

Drag the formula to all the cells to get the desired result, just like in the picture below.

Compare_columns_in_Excel_11

The following pictures show some of the failed comparisons displayed as errors. Simply modify the formula using the “IFERROR” clause like in the picture below to avoid errors.

Compare_columns_in_Excel_12.

Once done, you can either drag or copy-paste the modified formula to all the cells to get the result.

Compare_columns_in_Excel_13.

But their scenarios where the data might vary a little. For example, the comparison between the two cells might end up “FALSE” even if both the cells represent the same data. Let’s understand this better.

Take a look at the image below.

Compare_columns_in_Excel_14.

The data in columns 1 and column 3 are almost the same with a few additional extensions to a few cells. 

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

The standard VLookUp might end up delivering “FASLE” as a result. Use wildcards as a minor tweak to the data to avoid this, as shown below.

Compare_columns_in_Excel_15

Now, drag the modified formula cell to all the cells, and the would be as follows:

Compare_columns_in_Excel_16.

And this is how simple it is.

conclusion

Microsoft Excel contains all of the characteristics of a spreadsheet, using a grid of cells arranged in numbered rows and letter-named columns to organize and filter data operations. You can add rows and columns as many as you wish and store innumerable types of data.

Easy sheet is a tech blog where you can discover remarkable Excel hacks, tips, and tricks. Our purpose is to make Excel more comprehensible to learners and to provide them with Easy Sheet as a reference for all of their questions.

We are the best source for anyone in need of assistance with Microsoft Office products, including Excel, Word, PowerPoint, Publisher, and more!

About the author

Gaurav Singh Rawat

Add comment