How to compare two columns in Excel?

In this blog, you will learn What are the steps to compare two column in excel?

Before that, let’s go with some case study which shows how to compare two column in excel

Case Study 1:

Compare two columns for matches or differences in the same row

compare two columns in excel 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”,””)

Free Lessons on Excel VBA & Macro, Excel Dashboard and Advanced Excel: FREE VIDEO LESSON


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.

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

Case Study 2:

Compare two lists for case-sensitive matches in the same row

Now we will find an exact same name for case-sensitive matches.

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 or three columns based on our requirement, we just need to change the value of logical_test.

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.

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 two columns.

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018