Siam Hasan Khan
This is an interesting situation that often comes up. Namely, sometimes one needs to differentiate data into two different columns. There are so many processes in which Excel compares two lists and returns differences. In this article, we will see the ways on how to compare two columns in Excel for finding differences.
Download Practice Workbook
7 Ways to Compare Two Columns for Finding Differences in Excel
In this section, you will find 7 ways to compare two columns in Excel for finding differences. I will discuss them one by one here. Stay connected!
So, let’s get started with a simple example, to illustrate how to accomplish this.
Here we have two lists where some fruits’ names are placed. We will compare the two lists for finding the differences. The two lists containing the fruits’ names are given below.
We will see 7 different processes of finding the differences between the two columns. In every process of comparing and finding the differences between the two columns, we will use the same table.
1. Applying Conditional Formatting to Compare Two Columns
We can use Conditional Formatting to highlight the unique values of two columns. The procedure is simple and given below.
📌Steps:
- First, select the ranges where you want to apply the conditional formatting. In this example, the range is B5:B11.
- Now, in the Home tab click on Conditional Formatting, and under Highlight Cells Rules click on Duplicate Values.
- In the Duplicate values dialogue box, if you select Duplicate you will see the duplicate values of the two cells.
- If you select Unique in the Duplicate Values dialogue box you will see the unique values of the two cells.
- Press OK to confirm the Conditional Formatting.
Read More: How to Compare Two Columns or Lists in Excel
2. Compare Two Columns Using IF Function
We will use the IF Function to find the differences between two columns. Just follow the steps below for this.
📌Steps:
- First of all, create a new column to show which fruits of List 1 are available in List 2.
- Now, select the first cell (i.e. E5) of the newly created column and apply the following formula.
=IF(B5=C5,"YES","NO")
Here,
- B5 = Fruit in List-1
- C5 = Fruit in List-2
- After that, press ENTER, and you will see the statement NO in cell D5.
- Now, use the Fill Handle tool to drag down the formulated and Autofill the formula downwards from cell D5 to D11
- Hence, all the cells will show the result and you can differentiate between the two columns.
3. Applying EXACT Function to Compare Columns
The EXACT Function compares two text strings and then returns TRUE or FALSE based on the exact match between the texts. So, you can apply this function for serving the purpose of finding differences between two columns. In order to do so, chase the following steps.
📌Steps:
- First of all, select a cell and type the following formula to the cell.
=EXACT(B5,C5)
Here,
- B5 = Fruit in List-1
- C5 = Fruit in List-2
- Then, press ENTER and the cell will return FALSE.
- Now, drag the formula down and your cells will show you the result.
4. Applying IF with AND Function
A combination of IF and AND functions will serve your purpose. Proceed as below.
📌Steps:
- First of all, apply the formula to a selected cell.
=IF(AND(B5<>C5),"No Match","Match")
Here,
- B5 = Fruit in List-1
- C5 = Fruit in List-2
- Then drag the formula down to let the cells show the result.
5. Combining IF, ISNA, and VLOOKUP Functions
We can use the IF, ISNA, and VLOOKUP functions to find the differences between two lists or columns in Excel. The procedure is given below.
📌Steps:
- First of all, create a new column> select the first cell (i.e. E5) of the newly created column and apply the following formula.
=IF(ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)),"NO","YES")
Here,
- B5 = Lookup Value
- C5:C11 = Lookup Array
💡 Formula Breakdown
VLOOKUP(B5,$C$5:$C$11,1,0) looks for the value of B5 (i.e. Apple) in the range $C$5:$C$11. This value is not available in the lookup array and returns #N/A.
ISNA function checks whether a cell contains the #N/A! error or not. It returns TRUE or FALSE depending on the presence of #N/A!
So, ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)) = ISNA(#N/A) returns TRUE.
Finally, IF(ISNA(VLOOKUP(B5,$C$5:$C$11,1,0)),”NO”,”YES”) = IF(ISNA(#N/A),”NO”,”YES”) = IF(TRUE,”NO”,”YES”) = NO
So, the OUTPUT => NO. That is because the fruit name Apple from List-1 is not available in List-2.
- After that, press ENTER, and you will see the statement NO in cell D5.
- Now, use the Fill Handle tool to drag down the formulated and Autofill the formula downwards from cell D5 to D11
- Finally, you will be able to see the differences between List-1 and List-2
Read More: How to Compare Two Columns in Excel Using VLOOKUP
6. Use a Combination of IF, ISERROR, and MATCH Functions
Here we will be using IF, ISERROR, and MATCH functions to compare two columns. We will compare List-1 with List-2. The formula will calculate the two lists and will return the fruit’s name which is only in List-1. The procedure is given below.
📌Steps:
- First of all, select the first cell D5 of the newly created column and type the following formula to the selected cell.
=IF((ISERROR(MATCH(B5,$C$5:$C$11,0))),B5,"")
Here,
- B5 = Lookup Value
- C5:C11 = Lookup Array
💡 Formula Breakdown
MATCH function looks for the value of B5 (i.e. Apple) in the lookup range $C$5:$C$11.
So, MATCH(B5,$C$5:$C$11,0) returns #N/A as it doesn’t find the value in the lookup range.
Now, ISERROR(MATCH(B5,$C$5:$C$11,0))= ISERROR(#N/A ) returns TRUE.
Finally, IF((ISERROR(MATCH(B5,$C$5:$C$11,0))),B5,””) = IF(TRUE,B5, “”) returns the value of B5 (i.e. Apple).
So, the OUTPUT =>Apple.
- After pressing ENTER you will see the output in that cell. Now drag the following formula for the next cells.
- Hence, the cells in which you have copied the formula will show you the result.
- In this same way, you can find the fruit’s name which is only in List-2. In that case, the formula will be,
=IF((ISERROR(MATCH(C5,$B$5:$B$11,0))),C5,"")
Here,
- C5 = Lookup Value
- B5:B17 = Lookup Array
7. Combining IF and COUNTIF Functions to Compare Columns
In this procedure, if List-1 contains any fruit name which is not placed in List-2, the formula that we will be using will say that the fruit name from List-1 is not found in List-2. We will combine IF and COUNTIF functions for this purpose. Let’s start the comparison.
📌Steps:
- First of all, type the following formula in cell D5.
=IF(COUNTIF($C$5:$C$11, $B5)=0, "Not Found in List-2", "")
💡 Formula Breakdown
The COUNTIF function returns the total number of cells in a defined range.
COUNTIF($C$5:$C$11, $B5) looks for the value of cell B5 (i.e. Apple) in the range $C$5:$C$11 but finds nothing in the range. So, Output=> 0.
Finally, IF(COUNTIF($C$5:$C$11, $B5)=0, “Not Found in List-2”, “”) = IF(0, “Not Found in List-2”, “”) will return “Not Found in List-2” when the condition is 0, otherwise keep the cell blank (“”).
So, final Output=> “Not Found in List-2“.
- Now, press ENTER to let the cell show the result.
- After that, drag the formula down.
- By doing this, you will see the differences between the two columns.
Conclusion
So, we can see the different processes to compare two columns in Excel for finding differences. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed, using conditional formatting is the best way to compare two columns. Because in conditional formatting you can compare between multiple columns, the procedure is simple and fast and you can find both matches and differences.
Hope you find this article helpful. Browse our website for finding more useful articles. Keep in touch!
Good day
My apologies for the inconvenience
The files are currently not available for review/download
Thank you and have a nice day
Working Files are not found.
Could you resend alive links?
Tanks
Thanks for notifying me. It was a technical mistake. I’ve uploaded them and you can download now. Thanks.
Hallo Kawser,
This seems the path for an exact match, in case I would be ok with a first 6 numbers/characters match?
Thank you in advance
Hi, PAOLO!
Thank you for your query.
You have asked about a fantastic thing. If we want to compare the first 6 numbers/match, rather than the full cell value, we have to use the LEFT function in addition to the formulas.
So, I would suggest you use the second and third methods shown in this article and use the nested LEFT function inside the VLOOKUP function for the 2nd method and inside the MATCH function for the 3rd method.
So, for the second method, the formula would be:
=IF(ISNA(VLOOKUP(LEFT(A2,4),LEFT($B$2:$B$8,4),1,FALSE)),"NO","YES")And, for the third method, the formula would be:
=IF((ISERROR(MATCH(LEFT(A2,4),LEFT($B$2:$B$8,4),0))),A2,"")Have a nice day!