How to Compare two Columns in Excel Step-By-Step. Find difficulty in finding the same text in Excel, you can have a look for comparing two columns for matches or differences in the same row Like a two-dimensional database.
Excel is capable of storing many different types of data from small business contacts to personal income tax records. In both of these examples, accuracy is essential to make sure you have the information you need when you need it.
How to Compare Two Columns in Excel?
There mainly two ways to compare Two Columns in Excel as follows,
- Compare Two Columns for Matches or Differences in the Same Row
- The Compare Two Lists for Case-Sensitive Matches in the Same Row
Compare Two Columns for Matches or Differences in the Same Row
To compare two columns in excel you have to follow below step by step process,
Step 1: Create a table similar to the above picture.
- This table is showing the employee list of two departments.
- We will find the match of employee name between these IT and Account departments.
- Column C will find the match between Column A and B in the same row.
Step 2: In cell C2 we will use this formula
=If(logical_test,”Value_if_True”,”Value_if_False”) In our case: logical_test: A2=B2, Because we want to find match if the values of cell A2 and B2 are same Value_if_True: Match, If found A2=B2 then the value of cell C2 will be “Match” Value_if_False: “”, If found “A2 is not equal to B2” then the value of cell C2 will be blank (“”) So, In cell C2 our formula will be: =IF(A2=B2,”Match”,””)
Step 3: Value is showing blank
Press ENTER. Now in cell C2 value is showing Blank (“”), because there are no match between cell value of A2 (John) and Cell value B2 (Johm).
Step 4: Copy Formula & Dragging the File Handle
Copy this formula from cell C2 down to other cells by dragging the fill handle (a small square in the bottom-right corner of the selected cell C2). As you do this, the cursor changes to the plus sign.
Step 5: Result of matching between Column A & B
After copy the formula to another cell of column C we will get the result of matching between to column A and B as like the above picture. As a result, we can see row 4 and row 7 has the same employee name in both departments. (i.e. how to compare two columns in excel)
Tip 1: To find cells in the same row with different content, simply replace “=” with the non-equality sign: =IF(A2<>B2,”No match”,””) And do previous steps again. Then we will get a result as shown in the image.
Compare Two Lists for Case-Sensitive Matches in the Same Row
Now we will find an exact same name for case-sensitive matches. That is also called to compare two rows & columns in excel. To compare two columns in excel you have to follow below step by step process,
Step 1: in Cell B7 Type ‘SHAHRIAR’ instead of ‘Shahriar’. Delete all formulas from Column C.
Step 2: Now use this formula in cell C2: =IF(EXACT(A2, B2), “Match”, “”) Press ENTER and copy this formula to another cell of Column C.
the Final result will look like the picture above. Here we can see that after using case-sensitive match formula it found a match only in row 4. So, using IF function we can easily compare two columns based on our requirement, we just need to change the value of the logical_test.
In any data entry situation, people often transpose numbers wrongly or mistype a name in a spreadsheet. It is very difficult to tell the difference between 6886 and 6868 or ‘John’ and ‘Johm’ when you have long strings of numbers or text in a busy Excel worksheet. Imagine when you perform data analysis in Excel, one of the most frequent tasks is comparing data in each individual row.
This task can be done by using the IF function, as demonstrated in the following examples. Using Excel’s built-in function, you can make Excel do the work for you when you want to find out whether two cells contain exactly the same information. Continue to read to learn how you can automate the time-consuming task of checking for accuracy in your worksheets. There are many ways to compare the two columns.