This tutorial talks about how to compare two columns in Excel, which is a normal Excel job in our daily work. Comparing two columns can be done in different ways in Excel, such as comparing two columns row by row or cells by cells, comparing two columns for highlighting matches or differences, etc. Here this article covers most possible scenarios of the comparing two columns you might meet, and hope it can help you.
In this tutorial, it provides some example data for better explaining the methods on comparing two columns in various of cases. Based on your dataset, you may need to change or adjust some contents (references), however, the basic principles would remain the same. Or directly download the samples of each cases if you only want to verify if the methods work or not. Below is a data set where I need to check in the same row whether the names in column A are same with those in column B or not. Click to download the sample file
Generally, if you want to compare two columns row by row for exactly matching, you can use below formula: Press
If you want to compare two columns row by row for case insensitive, or get more description such as Match, Mismatch, you can use the IF function.
If you want to use the texts “Match” and “Mismatch” to describe the comparing results, please use below formula: =IF(EXACT(B2,C2),"Match","Mismatch") Press
If you want to compare cells for case insensitive, you can use below formula: =IF(B2=C2,"Match","Mismatch") Press
In above formulas, you can change the texts “Match” and “Mismatch” to your own description.
If you want to highlight the matching or different values, the 1. Select the two columns that are used to compared with (B2:C8, excluding column headers), then click 2. In the popping 3. Now click Or you can change the font size, font size, cell borders, or the number format to outstand the matches as you need in other tabs. 4. Click If you want to highlight the mismatch values, you can use this in the =$B2<>$C2 into the Then the differences of two columns in the same row will be highlighted with a specified color.
If you want to compare two columns row by row with VBA code, this tutorial satisfies you. 1. Enable the worksheet that contains the two columns used to compare, press 2. In the popping dialog, click 3. Then copy and paste below macro in the new module script.
4. press 5. Click
You can change the highlighting color based on your own need by changing the color index in the code, the Color index reference: In this part, the data set is shown as below, and you want to find all values which are in both column B and column C simultaneously, or, find the values only in column B. Click to download the sample file
Here you can use the formula which is combined with IF and COUNTIF function to compare two columns and find the values that are in column B but not in column C. =IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C") Press
1. This formula compares two columns without case sensitive. 2. You can change the description “No in C” and “Yes in C” to others. 3. If you want to compare two entire columns, change the fixed range $C$2:$C$8 to $C:$C.
The
1. Select two columns that you will compare with, then click 2. In the popping 3. Click
If you want to highlight the unique values (the differences) in two columns, also click The unique values will be highlighted.
If you want to highlight the values in column B that are also in column C, the C 1. Select the column B2:B8, click 2. In the 3. click You can use other formats to highlight the values in Font, Number, Border tabs. 4. Click If you want to highlight the values that only in column B but not in column C, repeat above steps, but change the formula in step 2 to =COUNTIF($C$2:$C$8, $B2)=0 in the Then choose another color to format the values.
Here the Conditional Formatting compares two columns without case insensitive.
Sometimes, after comparing two columns, you may take other actions on the matches or difference, such as selection, deletion, copy and so on. In this case, a handy tool – After free installing Kutools for Excel, please do as below: 1. Click 2. Then in the 1) In the 2) Choose 3) Choose 4) If you want to highlight the values, choose the color you need. 3. Click If you check the
If you want to compare with case sensitive, check the This tool supports to compare two columns in different worksheets.
If you want to list the matching values in another column after comparing two columns cell by cell, here the below macro code can help you. 1. Enable the sheet that you want to compare two columns, then press 2. Click 3. Then copy and paste below code to the new blank module script.
4. Press
5. Click
The VBA code compares two columns with case sensitive.
If you want to compare two columns cell by cell and then highlight the matches, you can try below code. 1. Enable the sheet that you want to compare two columns, then press 2. Click 3. Copy below macro code and paste them in the new blank
4. Press 5. Click 6. Click
1. The code compares columns with case sensitive. 2. You can change the highlighting color based on your own need by changing the color index in the code, the Color index reference: Sometimes, you may want to compare more than two columns in the same row, such as the data set as below screenshot shown. Here in this section, it lists different methods on comparing multiple columns. Click to download the sample file
To find full matches across columns in the same row, the below IF formula can help you. =IF(AND(B2=C2, B2=D2), "Full match", "Not")
If the cells in the same row match each other, “Full match” is displayed, or “Not” is displayed. Press
1. The formula compares columns without case sensitive. 2. If you need to compare more or equal to three columns, you can use below formula: =IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")
In the formula, 3 is the number of columns, you can change it to meet your need.
Sometimes, you want to find out if any two columns in the same row are matched, you can use below IF formula. =IF(OR(B2=C2, C2=D2, B2=D2), "Match", "Not")
In this formula, you need to compare any one pair of cells in the same row. “Match” indicates that there are any two cells matched, if there are no cells matching, the formula display “Not”, you can change the texts as you need. Press
1. This formula does not support case insensitive. 2. If there are many columns needed to compare, to compare any one pair of cells in the formula may be too long. In this case, you can consider to use below formula which combines the IF and COUNTIF functions. =IF(COUNTIF(C11:E11,B11)+COUNTIF(D11:E11,C11)+(D11=E11)=0,"Not","Match")
If you want to highlight the rows that all cells are matching with each other, you can use the 1. Select the range that you use, then click 2. In the Or
=AND($B2=$C2, $B2=$D2, $B2=$E2, $B2=$F2) Or 3. Click 4. Click
Above formulas do not support case sensitive.
If you want to highlight the row differences, which means it compares column cells one by one, and find the different cells according to the first column, you can use Excel built-in feature- 1. Select the range that you want to highlight row differences, and click 2. In the popping 3. Click 4. Now keep the cells selected, click
This method compares cells without case sensitive. Supposing there are two columns, column B is longer, and column C is shorter as below screenshot shown. Compared with column B, how to find out the missing data in column C? Click to download the sample file
If you only want to identify which data is missing after comparing two columns, you can use one of below formulas: =ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0)) Or =NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))
Press
Above two formulas compare data without case sensitive.
If you want to list the missing data below the shorter column after comparing two columns, the INDEX array formula can help you. In the below cell of the shorter column, supposing cell C7, type below formula: =INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))
Press Then you can remove the error value, and all missing data has been listed below the shorter column.
This formula compares cells without case sensitive.
If you want to do some follow-up operation on the missing data after comparing two columns, such as listing the missing data in another column or supplementing the missing data below the shorter column, you can try a handy tool- 1. Click 2. In the 1) In 2) Choose 3) Choose 3. Click Now you can press
Ticking the
Here is a macro code that can fill the missing data below the two columns. 1. Open the sheet that you use, press 2. Click
3. Then press 4. Click
The code compares cells without case sensitive. If there are two columns of dates as below screenshot shown, you may want to compare which date is later in the same row. Click to download the sample file
You can use the simple formula to quickly find whether the date 1 is later than date 2 in each row.
Press
1. In Excel, dates are stored as number series, they are numbers in fact. Therefore, you apply the formula to compare dates directly. 2. If you want to compare if date 1 is earlier than date 2 in each row, change the symbol > to < in the formula.
If you want to highlight the cells in column Date 1 if are greater than Date 2, you can use the 1. Select the dates in column B (Date1), then click 2. In the If you want to highlight the cells in column B that are smaller than those in column C, use the formula =$B2<$C2. 3. Click 4. Click For example, there are two tables, now you want to compare column B and column E, then find the relative prices from the column C and return them in column F. Click to download the sample file
Here it introduces some helpful formulas and a tool to solve this job.
In the cell F2 (the cell you want to place the returned value in), use one of below formulas: =VLOOKUP(E2,$B$2:$C$8,2,0) Or =INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)
Press
1. The formulas do not support case sensitive. 2. The number 2 in the formula indicates that you find the matching values in the second column of the table array. 3. If the formulas cannot find the relative value, it returns error value #N/A.
If you are confused with formulas, you can try the handy tool – After free installing Kutools for Excel, please do as below: 1. Select the cell F2 (the cell you want to place the return value), and click 2. In the
3. Click
If there are some minor difference between the two compared columns as below screenshot shown, the above methods cannot work. Please choose one of below formulas to solve this job: =VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0) Or =INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)
Press
1. The formulas do not support case sensitive. 2. The number 2 in the formula indicates that you find the return values in the second column of the table array. 3. If the formulas cannot find the relative value, it returns error value #N/A. 4. * in the formula is a wildcard which is used to indicates any character or strings. Below data set is an example for comparing and counting matches or difference. Click to download the sample file
The SUMPRODUCT formula can quickly count the matches in two columns. =SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))
Press
The formula counts cells without case sensitive.
If you want to count matches or differences between two columns, the handy tool – After free installing Kutools for Excel, please do as below: 1. Click 2. In the 3. Click Matching cells Different cells Supposing here is a list of data in column B, and you want to count the cells which contain ”Apple” or “Candy” in column D as below screenshot shown: Click to download the sample file To count if a cell contains one or more values, you can use a formula with wildcards to solve this problem. =SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))
Press Now if the related cell contains one or more values in column D, the result displays the number greater than 0, if it does not contain any value in the column D, it returns 0. If you want to count the total number of cells that contain the values in column D, use the formula in the below of cell F8:
1. Also you can use the formula to count if the cell contains values in another column =SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*")) This formula only needs to press 2. In the formulas, * is the wildcard which indicates any character or string. If you want to delete the differences or matches after comparing two columns as below screenshot shown: Click to download the sample file You can use the formula to find the difference or matches firstly: =IF(ISERROR(MATCH(B2,$D$2:$D$8,0)),"Difference","Match") Press Then apply the Select the formula column, then click Then the filter button appears in the column C, click at the filter button to expand the drop-down menu, choose the Now only the differences or matches have been filtered out. You can select them and press Now click Remove the formula column if you do not need it any more. There are two columns, one contains the original prices, and the other contains the sale prices. Now this part introduces a formula to compare these two columns and then calculate the percentage change between the two columns. Click to download the sample file You can use below formula to figure out the percentage change between the two prices in the same row.
Press Then format the formula result as percentage. Select the results, click The formula results have been formatted as percentages. Now you know how to compare two columns after reading above methods. However, in some cases, you may want to compare two ranges (two series with multiple columns) You can use above methods (the formulas or conditional formatting) to compare them column by column, but here introduces a handy tool – Click to download the sample file
Here are two ranges needed to be compared by cells, you can use the After free installing Kutools for Excel, please do as below: 1. Click 2. In the popping 1) In the 2) In the 3) In 4) Then in the 5) In the 3. Click Highlighting the same values
If you want to compare two ranges by row, you also can apply the
If you want to compare two ranges by row, the After free installing Kutools for Excel, please do as below: Supposing, the range F2:H7 is a model, now you want to find if the data in range B2:D7 is in the right order according to the range F2:H7. 1. Click 2. In the 1) Choose the two ranges into the 2) Choose the cell type you want to highlight in the 3) Choose the highlighting type in the 3. Click |