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.
Table of Contents
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:
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:
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.
Drag the formula to all the cells to get the desired result, just like in the picture below.
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.
Once done, you can either drag or copy-paste the modified formula to all the cells to get the result.
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.
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.
Now, drag the modified formula cell to all the cells, and the would be as follows:
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!
