How to compare two columns in Excel?

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.

Excel Power Users Ebook

Be Smart at Excel | New Skills for New JOB

This eBook will help you to get Excel Skills & Boost your career

Compare two columns for matches or differences in the same row

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.

inserting-the-formula-exactStep 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”,””)


Become a "Hero at Office"• 200+ Video Lessons • Real-life Case studies • Lifetime Access


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).

compare two columns in excel 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.

compare two columns in excel

Step 5: Result of matching between to 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 above picture. In the result, we can see row 4 and row 7 has the same employee name in both departments. (i.e. how to compare two column in excel)

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 result as shown in the image. compare two columns in excel

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 as compare two rows & columns in excel

Step 1: in Cell B7 Type ‘SHAHRIAR’ instead of ‘Shahriar’. Delete all formulas from Column C.

compare two columns in excel

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.

compare two columns in excel

Step 3: 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.