Simple function to compare two columns in Excel

When do you need to compare 2 columns in Excel

Excel is an intelligent tool that many users use in work and study. Excel is a widely used tool in office work because of its outstanding professional features. To check the difference in information between columns in Excel, you need to use the comparison function. This is a very necessary and important feature, especially if you use Excel for data entry.

Knowing how to compare data between 2 columns in Excel with a function will help you optimize work time and work on Excel quickly and conveniently. So what are you waiting for? Let’s find out how to compare 2 columns in Excel with a very simple function below!

How to use the EXACT function to compare 2 columns in Excel 

Advantages and disadvantages of using the EXACT function to compare 2 columns in Excel: 

• Advantages: Easy to use, quick comparison. 
• Disadvantages: It distinguishes between uppercase and lowercase letters, data can only be compared horizontally. 

The syntax of the function is: =EXACT(Text1;Text2) 

Apply as follows: 

• Text1: The first data to be compared. 
• Text2: The second data to be compared. 

Some notes when using the EXACT function to compare 2 columns in Excel:  

• The EXACT function compares two text strings, returns True if the two strings are the same and returns False if the two strings are different.
• The EXACT function differentiates between uppercase and lowercase when comparing. 

To make it easier to understand, here is a specific example: 

Below I have 2 columns to compare as shown in the image below. 

Step 1: In the cell you want to compare, enter the formula with the syntax “=Exact($A$2:$A$7;$B$2:$B$7)” 

Where: 

• $A$2:$ A$7: The data in column 1 to be compared. 
• $B$2:$B$7: The data in column 2 to be compared. 

Note: In this formula, I put 2 addresses in the formula as absolute addresses so that when you drag it down to apply to the following cells, the result will not be incorrect.

Step 2: Press Enter and see the return value.

Where: 

• True: The data is the same.
• False: The data is different. 

Step 3: Press and hold the small square box in the corner of the cell you just entered the formula, then you drag it down to apply to the cells below. 

As you can see, after entering the function and pressing Enter, it will return “True” and “False”. In this case, all uppercase fruit types in both columns return true, and the remaining fruit types return false.

Using the COUNTIF function to compare in Excel 

Advantages and disadvantages of using the COUNTIF function to compare in Excel: 

• Advantages: You can accurately compare 2 columns of data without distinguishing between uppercase and lowercase. 
• Disadvantages: The execution steps of the function are quite long and complex. 

The syntax of the function is: =COUNTIF(Range;Criteria) 

Where: 

• Range: The range of data to count (required parameter). 
• Criteria: The condition to count the data (required parameter). 
• The Countif function counts the number of cells that meet certain conditions in the selected data range.

To have a clearer picture, here is an example:

Step 1: Name the data column 1. At this step the first thing you need to do is “select the first column containing data”. Then you “go to the address cell” and “enter the name Danhsach1” and then “press Enter”. 

Step 2: “Name the data column 2.” This step is the same as Step 1 but it is for column 2.

Step 3: You “scan select the entire column data Danhsach1″ and then “select the Home tab”.

Step 4: Then you “select Conditional Formatting” and “select New Rule”.

Step 5: When the dialog box appears, you “select Use a formula to determine which cells to format”.

Step 6: You “enter the formula =COUNTIF(Danhsach2,A2)=0” and then you “select Format” and you’re done.

Explanation of the formula: 

This formula means that the fruits in Danhsach2 are calculated and compared with the value of Danhsach1 from cell A2. 

As for the part =0, it means that if there are no fruits similar to Danhsach1 in Danhsach2, Excel will consider this value as 0, and we format this 0 value in the same color as the example.

Step 7: Continue when “the Format Cells dialog box” appears, you “select the Fill tab” and choose a color to mark the type of fruit not in Danhsach2 similar to the example and “press OK”.

Step 8: Then you “press OK again” to apply the same to Danhsach1.

Step 9: Scan “select the entire column data Danhsach2” then “select the Home tab”.

Step 10: Then you “select Conditional Formatting” and “select New Rule”.

Step 11: When the dialog box appears, you “select Use a formula to determine which cells to format”.

Step 12:  Finally, you “enter the formula =COUNTIF(Danhsach1;C3)=0 and select Format” and you’re done.

Explanation of the formula:

This formula means that the fruits in Danhsach1 are calculated and compared with the value in Danhsach2 starting from cell C3. 

As for the part =0, it means that if there are no fruits similar to Danhsach2 in Danhsach1, Excel will consider this value as 0, and we format this 0 value in the same color as the example.

Step 13: Continue when “the Format Cells dialog box” appears, you “select the Fill tab” and choose a color to mark the type of fruit not in Danhsach1 similar to the example and “press OK”.

Step 14: Then you “press OK again” to apply the same to Danhsach2 and you’re done.

After comparing it, you will get the result as shown in the image below.

Common errors when comparing data between 2 columns in Excel

Comparing data between two columns in Excel is not too complicated. Therefore, anyone can easily and quickly perform it to work comfortably with Excel. When comparing data between 2 columns in Excel, you should pay special attention to fixing the following errors: 

Data mismatch error

Data mismatch errors when comparing data between 2 columns in Excel can occur for various reasons, such as: entering data incorrectly or inaccurately, data is sorted in a different order. To solve this problem, please do the following: Check the data carefully to see if the entered data is accurate. 

Formula syntax error 

In addition to data mismatch errors, formula syntax errors are also the most common errors. The causes of this error are: entering the wrong function name, the wrong function syntax, using the wrong cell reference, etc. To solve this problem, there is no way other than entering the correct formula syntax. Furthermore, you must ensure that the cell references in the formula are correct. 

Incorrect data range error

Incorrect data range errors often occur for many reasons, such as: selecting the wrong data range and the selected range having a different format. Fixing this error is extremely easy, all you need to do is make sure you select the correct data from the comparison range and ensure that the selected range has the same format.

Duplicate data error 

Duplicate data errors often occur when working with Excel. You should pay special attention to this error to make the working process as fast and proactive as possible. To fix this error, you can use the search function to search for cells with duplicate data.

Incorrect data format error

Incorrect data format errors also cause errors when comparing data between 2 columns in Excel. To fix this error, you need to use the formatting tool to format the data in both columns the same.

You may also like

Simple Formula and Instructions for Using the SUBTOTAL Function in Excel

The SUBTOTAL function in Excel is used to calculate the sum of a range of numbers with specified conditions. It helps individuals save time and improve work efficiency. If you are unfamiliar with how to use the SUBTOTAL function, be sure to check out the article below from FPT!

What is the Excel Right function? Simple and illustrated examples on how to use the Excel Right function.

The Right function in Excel is used to extract the characters you want to retrieve in Excel. When you have a list of file names, email addresses, or other information, you can use the Right function in these cases. Let’s explore what the Right function is!

What is the COUNTBLANK function? Applications and effective uses of the COUNTBLANK function

COUNTBLANK function in Excel is used to count the number of empty cells in a data table. This function is widely applied in both academic and professional settings. If you are unfamiliar with how to use this function or have forgotten, then follow the article below to learn how to use the COUNTBLANK function.

How to Combine QUERY and SUM in Google Sheets: Simple and Effective Guide

Combining QUERY and SUM in Google Sheets allows you to quickly perform calculations. There are certain problems that cannot be solved using a single function, so it requires combining multiple functions. This article will guide you on how to combine QUERY and SUM!