Compare Two Columns In Excel (Ultimate Guide!)

When dealing with Data analytics, comparing data populated in two or more columns is common practice.

Manually comparing data could take hours or even days, depending on the amount of the data in question.

But when it comes to using Excel, you can expect data work to be done in a matter of minutes

The same is true for comparing two columns in Excel.

Why Compare Columns in Excel?

You may want to Compare Columns in Excel to find matching, or non matching data, or specific information.

The first step to compare columns typically involves comparing data by sorting it into two or more adjacent columns.

This is one of the quickest techniques in Excel and can compare any amount of data in less than a second.

excel-promo-2

Exact Row Match

When comparing two cells, Excel matches the same row of the first column against the second column to find the similarities or differences.

The top four methods for comparing two columns are listed below:

Let’s explore these methods and related examples below:

Compare Two Columns Using a Simple Formula

Look at the following datasheet. It reflects the record of 10 employees in an organization displaying their log-in and log-out time.

Column A represents the names of the employees, whereas Column B and Column C display their log-in and log-out times, respectively.

Data representing employees’ names, log-in and log-out times

In this example, our objective will be to highlight the employees who forgot to log out using a simple Microsoft Excel technique.

Since the office timings begin from 8:30, so the employees who didn’t log out will have the same log-in and log-out time.

Let’s now compare the columns and see the results.

We use the comparison operator “=” (equal to) to find the exact match in two columns. It is used as follows:

Step 1

In cell D4, enter the operator “=” in the formula bar and select cell B4.

Use the “=” operator and select the first column

Step 2

Enter the “=” operator again, followed by selecting cell C4.

Use the “=” operator again and select the second column

Step 3

Now press the “Enter” key. You get a “false” value in return; this implies that the value in cell B4 is not equal to that of cell C4.

Press Enter to get the result

Step 4

Now drag the cell handle or copy-paste the formula to the remaining cells.

The output will be either “True” or “False,” as shown in the figure above.

Assuming that the column B and column C have the same values, you obtain “True” in the result and vice versa.

Here, ‘True’ means that the log-in and log-out time match, which in turn suggests that the employee forgot to log out.

In contrast, False means that the log-in and log-out entries do not match each other.

Hence, as evident from the gif, two employees, John and Charles, did not log out properly.

Copy the cell formula to get the results

Note: You may employ the filter function to immediately sort out the rows with the corresponding value ‘True’ in column D.

This will help you filter out the employees who forgot to log out.

Compare Two Columns Using the IF Function

Considering the above example again, let’s now compare the two columns, B and C, using the IF Function.

The result of the comparison must be something like this:

    • “Forgot log out” if the employee forgot to log out
    • “OK,” if the employee logs out properly

The syntax of the IF function for this comparison is as follows:

=IF (B4=C4,“Forgot log out”,“Ok”)

Since the IF function makes a logical comparison between two values, let’s see how the formula works before getting into its example.

The IF Function compares two columns, and if they are matching values, the Function returns “True”; otherwise, it returns “False.” Now for every “True” value, it displays a specific text.

In this case, Microsoft Excel displays the message, “Forgot log out,” for every true argument.

Similarly, the formula is set to return the message “Ok” for every “False” value.

If the technical language here is a bit confusing, we cover logical functions from the ground up in our Excel training.

Let’s apply it to our data as follows:

Step 1

Put the above IF Function in cell E4 and press Enter to get the output.

Comparing two columns using IF Function

Step 2

Drag the cell handle or copy-paste the cell formula into the remaining cells to obtain the output.

Drag the cell handle to obtain the result

The Function displays Forgot log-out message where both the cells have matching values, and displays OK, where the same values are different.

This Function can be pretty helpful when dealing with a large list of items and matching their properties.

Compare Two Columns Using the EXACT Function

Considering the data in the above example again, let’s use the EXACT Function to compare two columns.

The syntax of the EXACT Function is as follows:

=EXACT (B4, C4)

Step 1

Put the above Exact formula in the output cell F4.

Put the EXACT formula in the output cell

Step 2

Press the Enter key to get the following result.

The result of the EXACT formula

Step 3

Drag the cell handle to copy the formula in the remaining cells.

The output of the EXACT formula

Comparing both columns with the EXACT Function shows two true cases where John and Charles forgot to log out properly.

Tip: Note that the Exact Function is case sensitive, i.e., treats uppercase and lowercase characters differently.

For an extra tip, check out how you can use AI with Excel to generate the formulas for you!

Comparing Two Columns Under Effect of EXACT Function’s Case Sensitivity

To see how the EXACT Function works with case sensitive data, let’s see the example below:

The Exact Function is case sensitive

Using the same data as in the above examples, we changed the letter case of the names.

Let’s now compare both the columns using the EXACT Function. The syntax of the EXACT Function looks something like this:

=EXACT (A4, B4)

Step 1

Insert the syntax in the formula bar by selecting cell E4 and pressing Enter. The result will be displayed in the cell as:

The result of the Exact Function

Step 2

Drag the cell handle to copy the EXACT formula in the remaining cells.

Drag down to get the result of the Exact Function 

As evident from the image above, the EXACT Function returns “False” when the casing of Cell A is different from Cell B and vice versa.

Compare Two Columns Using Conditional Formatting

Another feasible method is using conditional formatting. It comes in handy when you want to compare data in two columns and do not want the result to be displayed in the third column. You can find the matching entries within the two columns using conditional formatting.

Consider the following example:

Compare Two Columns Using Conditional Formatting 

To highlight the matching columns in the above image, follow the steps given below:

Step 1

CTRL+A to select the entire data. In the “Home” tab, select “Conditional formatting.” From the drop-down list, select “New Rule,” and the “New Formatting Rule” window appears.

Compare Two Columns Using Conditional Formatting 

Step 2   

From the new formatting rule dialog box, select “Use a formula to determine which cells to format.” The drop-down list will expand to get the formula for the cell formatting.

Use a formula to determine which cells to format   

Step 3

Now enter the formula “=$B4=$C4” under the “edit the rule description.”

Entering a formula for conditional formatting 

Step 4

Once done, click the “format” button, and a Format Cell window will appear. From the format Cell window, select the “Fill” option from the top menu and select the color that you want to fill in the identical columns. Click the “Ok” button twice, and you’re done.

Formatting the cells you want to compare 

The matching entries of column B and C are highlighted as shown in the figure below:

The result of the Conditional Formatting Feature 

It is clear from the results that the log-in and log-out columns of John and Charles are identical, indicating that their log-out time was different than others.

This is how you can use the conditional formatting feature to compare data using your customized formulas to get the desired results.

Highlight Matching Data

Like the method above, you can use conditional formatting to compare two columns by comparing each cell of both columns and not involving a row-by-row comparison.

Consider the following example:

Compare Two Columns - Highlight Matching Data

To highlight the matching data in two columns, follow these steps:

Step 1

Select the entire data set and from the “Home” tab, select “Conditional formatting.” In the drop-down list, hover the cursor over the “Highlight Cell Rules” and select “Duplicate Values” from the list. From the Duplicate Values dialog box, select the color scheme that fills the cell matching in both columns. Once done, press OK.

Using Duplicate Value cells feature

The result will be something like in the image below:

The result of the Conditional Formatting feature 

 

The conditional formatting function highlights the matching entries in both columns.

Tip: Must note how the Conditional Formatting duplicate rule is not case sensitive. Therefore, conditional formatting can be used to compare two columns if case sensitivity is not your concern.

Find Missing Data

You can use any of the two methods below to find any mismatched data in your column set.

These involve Excel formulas and formatting features making the process quick and fun!

1 – Finding Mismatched Data Using Formula

The first method is based on the use of a formula to find mismatched values. For that, let’s see the example below.

Finding Mismatched Data Using Formula   

Step 1

Select the entire data set. Go to the “Home” tab and click the “Conditional formatting” tab. From the drop-down list, select the “New Rule” option and from the “New Formatting Rule” window, select “use a formula to determine which cells to format.”

Select the “New Rule” option  

Step 2

To highlight the mismatched values, enter the following formula in the Edit formatting rule dialogue box under the box that reads “Format values where this formula is true”.

=$A4<>$B4

Input the formula to find the mismatched data 

Step 3

Click the “Format” button to select your desired color for mismatched data, and choose the color from the “Format Cell” window. Press the “Ok” button twice, and tada! You’re done.

Use the Format feature to display mismatching data results

The highlighted cells are visible in the image below:

The result of the Conditional Formatting feature 

In the above image, all the mismatching rows in both columns have been highlighted using Conditional Formatting, whereas the matching data remains unchanged.

2 – Finding Mismatched Data Using Conditional Formatting

Excel also offers features that help you find data in a single column.

Conditional formatting lets you highlight data from your own defined rules.

Consider the following example:

Finding Mismatched Data using Conditional Formatting

Step 1

Select the entire data. Go to the “Home” tab and then select the “Conditional Formatting” tab. Move the cursor on the “Highlight Cell Rules” and select the “Duplicate Values” option from the drop-down list. You will see the Duplicate Values dialog box on your screen as follows:

The Duplicate Values window 

Step 2

Select the option “Unique” and specify the formatting for those unique data.

The Unique Value option

Step 3

Press “Ok” to get the results.

The result of the Conditional formatting 

The image displays unique data highlighted in both columns as below:

Highlighted the missing data

Since the Conditional Formatting feature is not case sensitive, it considers “David” and “david” the same entries. However, if your work calls for dealing with case-sensitive data, you can go for the other diverse options mentioned in this article.

VLOOKUP

When you want to compare two columns in Excel, the VLOOKUP function is the king. It offers multiple options to compare data.

Let us see how to use the VLOOKUP function to its full potential while comparing two columns in Excel.

Consider the following example:

Compare Two columns using VLOOKUP Function 

The above image displays two lists of various companies’ names. We will use the VLOOKUP Function to compare multiple columns and find the matching entries.

Step 1

Let us suppose we want to compare List A to List B to find the common names in both columns. Here is how we can use the VLOOKUP function for the said purpose.

Applying VLOOKUP Function 

Step 2

Insert cell B4 in the VLOOKUP formula as we want the values in List B to be looked up.

Applying VLOOKUP Function 

Step 3

In the next argument, the Table array (where the values are to be looked up for) is set to List A. Select the range of cells from A4 to A11 and make it an absolute cell reference by pressing F4.

Select the cell range for the next argument 

Step 4

The next argument is the “Col Index Num,” number of the column from left to right from which the result is to be obtained. Since List A consists of only one column, so the “Col Index Num” will be 1. Insert this value in the Function.

Select the “Col Index Num” as 1 

Step 5

The fourth and last argument to the VLOOKUP function is an optional one. You may set it to FALSE or leave it omitted to Excel to set it to FALSE by default.

Select FALSE as the fourth argument

Step 6

Once you’re done with the Function, close the bracket and press “Enter” to get the results.

VLOOKUP applied in Excel 

It is clear from the result that the cell value Accenture is not available in List A. #N/A equates to the fact that the values do not match.

Step 7

Drag the cell handle and copy the VLOOKUP function in the remaining cells.

Drag the cell handle to get the result 

VLOOKUP facilitates a quick comparison of data populated within multiple columns.

However, in the case of ‘no match, it returns the error “#N/A”. This may confuse inexperienced users, making them think that something is wrong with the formula.

To remove this Error message, replace it with a blank by using the IFNA function. The syntax of the IFNA function for this comparison will be something like this:

=IFNA(=VLOOKUP(B4,$A$4:$A$11,1,FALSE),””)

 

Step 8

Insert the IFNA function in the previous example and press “Enter.”

Drag the cell handle to get the result 

Step 9

Drag the cell handle to copy-paste the IFNA function in the remaining cells. The result should look something like this:

Drag the cell handle to get the result

It is evident from the image above that “KPMG Pvt Ltd” is the only company name that exactly matches both column A and column B.

Tip: Notice your VLOOKUP running slowly? Learning to use VLOOKUP vs INDEX & MATCH will help you stay efficient, even while working with bigger data sets.

Using VLOOKUP For Partially Comparing Two Columns In Excel

In the above example, a few names exist in both the column A and column B, but are followed by suffixes or prefixes.

Until the values are in absolute conformity (letting aside the letter case), Excel matching or comparing functions would fail to fetch the desired results.

However, the VLOOKUP function has the capability of handling such situations using the “Wildcard Characters.”

You can use the wildcard character (*) that enables the VLOOKUP Function to look up values before and after the selected data. Let us see the use of (*) to get the desired match.

Step 1

Modify the above formula and add (*) as given below:

=IFNA(VLOOKUP(“*”&B4&”*”,$A$4:$A$11,1,FALSE),””)

Step 2

Drag the cell handle to copy-paste the formula into all the cells. The result will look something like this:

Put the Wildcard character in the VLOOKUP function 

Before using the wildcard character ‘asterisk,’ Excel returned only one match, but upon using the wildcard character, the formula returned three more matches. Weird, no?

Let’s see the science behind this change.

The wildcard character asterisk matches any number of characters. As we applied the asterisk before and after the data value of each cell of list B, it returned every entry from List A that had the text string from List B.

For example, cell B5 contains CCD, whereas A7 contains “Coffeeday Ltd (CCD)”. Using the wildcard character ‘asterisk’ matches and gives back all results from Column A that have the text string CCD.

The same is true for “Mind Tree” and “Apple” present in B9 and B10.

Here’s how seamless the process is.

Compare Two Columns - Pull Matching Data  

Pull Matching Data

Comparing multiple columns in Excel is a normal practice and is usually utilized in making administrative decisions.

By comparing the data columns, you can pull out information to analyze the data, and the VLOOKUP function is extremely helpful when it comes to matching data.

Let us now see how you can use VLOOKUP to draw the matching data in another column.

Consider the following example:

Compare Two Columns - Pull Matching Data 

List A consists of companies’ names, whereas the Market Value column contains their market price, respectively.

Suppose we want to fetch the market value for List B.

To do that, we will use the VLOOKUP Function to find values in List A and then fetch the corresponding market value from List B.

Step 1

Insert the VLOOKUP formula in cell E4.

Compare Two Columns - Pull Matching Data 

Step 2

We want to look up the market value of the companies from List B, so we select cell D4 as the lookup value.

Select the lookup value 

Step 3

Since we want to find the market values of the selective companies from List A, we select the “Table array” range from A4 to B11 and make the cell reference absolute by pressing F4.

Select the table array argument

Step 4

To fetch the market price of the selective companies, we insert the column index of the market value data column, which is 2.

Notably, the Column Index is counted from left to right in the selected table array.

Select the third argument Colum Index

Step 5

We enter False or 0 as the last argument in the VLOOKUP Function for an exact match to obtain the results.

Select “False” for the exact match 

Step 6

Once done with the VLOOKUP formula, close the bracket. Since we have learned in the previous example that the VLOOKUP function returns a #Num error for a mismatch, you may want to nest the above Function into the IFNA function for better results.

The formula will look something like this:

=IFNA(VLOOKUP(D4,$A$4:$B$11,2,False),””)

Now put this formula in cell E4 and press “Enter” to get the results.

Use of IFNA function to remove the #Num error

Step 7

Drag the cell handle to copy-paste the VLOOKUP formula into all the cells.

Drag the cell handle to get the results 

 

Pulling Matching Data

The above example represents the instance where the VLOOKUP function matched and returned the values that were an absolute match.

However, it did not work the same for “Apple” and “Apple Inc.” Here’s what you can do to look up values having prefixes or suffixes.

In the previous example, we learned how to use the wildcard characters to fetch the partially matched cell values. We can apply the same wildcard character (*) here to fetch the partially matched data while comparing two columns in Excel.

The formula for fetching partially matched data looks like:

=IFNA(VLOOKUP(“*”&D4&”*”,$A$4:$B$11,2,FALSE),””)

Step 1

Put the above formula in cell E4 and press “Enter” to get the result.

Using the wildcard character to fetch the partially matched data

Step 2

Drag the cell handle and copy-paste the above formula into the remaining cell to get the partially matched data.

Drag the cell handle to get the results 

The above image shows how the VLOOKUP function finds data with partial matching using the asterisk (*) character.

Cell D5 contains the value “Infosys,” whereas A5 contains the value” Infosys Limited.”

Using the asterisk (*), VLOOKUP fetches those values even if they are not an absolute match.

Here’s a glance at how you can pull the matching data using the VLOOKUP function while comparing two columns in Excel.

Drag the cell handle to get how you can pull the matching data using the VLOOKUP functionresults  

Example Of Pulling Data by Comparing Two Columns

Since we are now well versed with the methods of comparison of two columns in Excel and pulling data in the third column using the VLOOKUP function, let us use the techniques altogether.

Consider the following example.

Practical example of comparing two columns

The above example shows employees’ records of their log-in and log-out time along with their company’s ID. Using this template, you can fetch the record of any employee by using the first three letters of their name.

Let us see how it works as below:

Step 1

Since the template works by using the first three letters of the employee’s name, the focus is placed on the Name column, i.e., G4.

To obtain results with only the first three letters of the employee’s name, the VLOOKUP function needs to work together with an asterisk.

The first argument of the VLOOKUP function will look something like this:

=VLOOKUP (G3&”*”,

This argument is for the value of cell G3 with any number of letters on its right side. Insert the Function in the cell as:

Putt first argument values for partial lookup 

Step 2

For the table array, select the array range from A4 to D12 and turn it into an absolute reference.

Selecting Table array for look_up values 

Step 3

Since we want to look up the Name column, which has the index 1 in the selected column array, the “Col_index_Num” is set to 1.

Selecting the Col_Ind_Num 

Step 4

For an exact match of the Name value, we put “False” as the last argument in the VLOOKUP function.

Selecting the FALSE value for an exact match 

Step 5

Once done with the VLOOKUP function, close the bracket and press Enter. Entering the first three letters in cell G3, Excel gives back the following results.

Selecting the FALSE value for an exact match 

Step 6

For fetching the ID, let’s consider the value of G4 as the Look_up value. Since we are looking for an ID value that has Col_Ind_Num 2 in the selected table array, the Col_Ind argument is set to 2.

And for an exact match, we will add “FALSE” to the VLOOKUP function. The VLOOKUP function for fetching ID values will look like this:

=VLOOKUP (G4,$A$4:$D$12,2, FALSE)

Put the above formula in cell G5 and press Enter.

Selecting the FALSE value for an exact match 

Step 7

For Log In time, we will use the same formula as we used for ID value; the only difference will be the Col_Ind number. It will be 3 for log-in time. The Function will look like this:

=VLOOKUP (G4,$A$4:$D$12,3, FALSE)

Put the above formula in cell G6 and press Enter.

Fetching the Log-in time by using the VLOOKUP function 

Step 8

For fetching Log out time, use the same formula with Col-Ind_Number as 4. It will look like this:

=VLOOKUP (G4,$A$4:$D$12,4, FALSE)

Put the formula in cell G7 and press Enter.

Fetching the Log out time by using the VLOOKUP function 

Step 9

The Working hours reflect the total time that an employee spends in the office. To calculate working hours, we will subtract the Log out time from the log-in time.

To do so, let’s bring to use the TEXT function. The syntax for the TEXT function for calculating working hours would take shape as follows.

=TEXT (G7-G6, “h:m”)

Put the above formula in cell G8 and press Enter.

Using the TEXT function for calculating working hours 

Step 10

The Conditional Formatting feature makes an appropriate choice to format the cell by comparing all the cells.

If any employee logs in after the standard log-in time, the template should reflect it as follows.

Using Conditional Formatting to format cell 

Here is how the template works. It compares multiple columns and fetches the required data using the VLOOKUP Function.

Not only that, it automatically calculates the working hours for each employee and indicates if any employee logged in late by changing the color of the cell.

Using Conditional Formatting to format cell 

Use Cases of Comparing Columns

Comparing two data columns having 5 or 10 entries is a piece of cake.

But what if you have thousands of data entries in both columns?

In case you are a beginner, let’s first familiarize ourselves with what the comparison of two columns in Excel is used for.

We have listed some use cases below:

  • Maybe you want to find the top-selling mobile phones at your store – you can compare the sales of mobile phone data to find the top selling phone.
  • Perhaps you want to find out which of your favorite football players qualified for a tournament – here you can compare your favorite players with qualified players and find out.
  • If you want to know the availability of certain gadgets from a huge list of items at your store – you can fetch the data by comparing your required items with the store inventory.
  • Similarly, you can find out the share price of your stock by comparing it with the stock data in a few clicks!

There are hundreds of other cases which involve comparing columns.

Troubleshooting:

Comparing columns in Excel is a dynamic exercise – you can bring to your use a wide variety of functions, formulas, and tools to help the job.

However, if you get improper results while comparing two columns in Excel using the functions demonstrated above, do check for the following common problems.

    • #N/A Error

It is not an error in nature. Instead, it is a system message returned when you use the VLOOKUP Function to compare two columns for an exact match. In case of a mismatch, the VLOOKUP function returns the message #N/A.

To avoid such error messages, you can use the IFNA function to replace this error message with an empty cell instead.

The syntax for the IFNA function is as follows:

=IFNA (VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup]),””)

Let’s recap on how to resolve this issue quickly.

Resolving #N/A error 

    • #NAME? Error

If you get this error while using the VLOOKUP function, check out function syntax. One of the possible reasons for this error is the wrong spellings of “True” or “False” in the arguments.

Resolving #NAME? error 

    • #VALUE Error

Finding a #VALUE error indicates that you might have made some mistake while writing the VLOOKUP argument. Removing the syntax error can rid you of this error message.

Resolving #VALUE error

Conclusion:

Excel provides a variety of methods and functions to compare two columns, including the use of a simple operator “=”, the EXACT Function, the IF Function, and the Conditional Formatting feature

Not limited to the above, you may use the VLOOKUP function for performing column comparisons in Excel.

The VLOOKUP function is undoubtedly one of the most powerful functions of Excel to compare two columns in Excel and fetch matching or partially matching data into another column.

Practice the techniques discussed above to become an Excel whiz in no time. Happy Excel Exploring!

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.