How to Compare Two Columns in Excel (6 Quick & Easy Ways)

Excel is champ for organizing and analyzing data. When it comes to analyzing, of course there will be comparisons. For your own benefit or your superior's, you need to note and highlight the findings of the comparisons. That's what's in store for you today in the form of this tutorial where you will learn how to compare two columns in Excel. You will see the use of the humble equals operator, the EXACT function, the IF function to return custom results, and a great deal of highlighting because analytically, it really is what grasps the eye first.

How you will go about the comparison depends on the data layout and what you're trying to get out of the data. Finding whether two cells are matching in value or case type, highlighting matching values or mismatching values, extracting matches, we've got it all covered. Now that we've got you covered…

Let's get comparing!

How To Compare Two Columns In

Method #1 – Compare Cells in the Same Row (side by side)

The very first comparison of two columns is going to be a simple row by row, line by line comparison. Our first test is checking whether the values in one column match the values in the adjacent column. The methods below are relevant to exact matches between the two columns and will not overlook even a single space character's difference between the columns. Let's begin with the methods of comparing cells in the same row.

Using Equals Operator

Using the equals operator "=" we can compare the values in two columns for equalness. As an example, we will be working on comparing shipping and billing addresses to see if they match each other. Here is the formula to compare the value of two cells using the equals operator:

=B3=C3

The simple logical test here is to check if cell B3 equals C3 or not. The shipping address in B3 matches the billing address in C3 and so the formula returns TRUE. For any instance where the value from column B would not match its joining cell in column C (e.g. B5 and C5), the formula will return FALSE.

Compare Cells in the Same Row (side by side)

Using IF Function

The IF function checks whether a condition is met and returns a specified value for TRUE and FALSE each. Taking the method and example from above, we can refine the results to get the specified values instead of TRUE and FALSE. Let's see the formula below for comparing two columns using the IF function:

=IF(B3=C3,"Same","Check Billing")

The condition given for the IF function to assess is the logical test used in the example above. We are checking if the values in column B match the values in column C, row by row. IF checks if B3 equals C3. If this is true, we have assigned IF to return the value "Same" instead of TRUE and to return "Check Billing" if FALSE. The addresses in B3 and C3 are the same and so IF returns "Same".

The addresses in B5 and C5 do not match and IF is set to return "Check Billing".

Compare Cells in the Same Row (side by side)

Using EXACT Function

The EXACT function is a case-sensitive function that is used to check if two text strings are exactly the same. Now let's try to compare two lists of names copied from different sources to see if they are the same, using the EXACT function.

Down to its bare essentials, the EXACT function can be used like so:

=EXACT(B3,C3)

to return TRUE or FALSE. We can incorporate the IF function here to give us customized results and we're going to do just that. The following is the formula with the EXACT and IF functions for comparing two columns:

=IF(EXACT(B3,C3),"Match","Check source 2")

The EXACT function checks the text in B3 and C3 to be exact case-sensitive matches. We find C3 to contain the surname in uppercase which is differing from the name in B3 which is in capitalized initials. The EXACT function results in FALSE and the IF function returns FALSE as our supplied text string "Check source 2".

In the second row of the dataset, B4 and C4 are the same case type. The IF function takes the resulting TRUE from the EXACT function and returns "Match".

Compare Cells in the Same Row (side by side)

Method #2 – Compare & Highlight Cells with Matching Data (side by side)

Perhaps you don't want to have a resulting column with TRUEs and FALSEs or their equivalents. For a quick look, it'd immediately be more noticeable to have the matching cells highlighted. This is easily achievable using the very handy Conditional Formatting feature in Excel.

Conditional Formatting changes the appearance of a cell to show if a certain condition is met. Here are the steps to highlight cells with matching data with Conditional Formatting:

  • Select the cells in the dataset for comparison.
Compare & Highlight Cells with Matching Data (side by side)
  • While in the Home tab, click on the Styles group's Conditional Formatting button and select the New Rule… option from the menu.
Compare & Highlight Cells with Matching Data (side by side)
  • This will open a New Formatting Rule window.
  • First, select the rule type Use a formula to determine which cells to format.
Compare & Highlight Cells with Matching Data (side by side)
  • Next, enter the following formula in the provided field:
=$B3=$C3

This is a simple formula using the equals operator to check if B3 equals C3. Columns B and C are locked into absolute references using the $ sign.

Compare & Highlight Cells with Matching Data (side by side)
  • Now you can click on the Format… button to choose the look you want the cells with matching values to have. You can format by way of cell color fill, font style, etc.
Compare & Highlight Cells with Matching Data (side by side)
  • When done, click on the OK command button of the New Formatting Rule

The cells with matching values in both columns B and C will be highlighted in the chosen format:

Compare & Highlight Cells with Matching Data (side by side)

If your data has fewer anomalies than regularities, you can flip the condition to only have the mismatched cells highlighted by adding "=FALSE" to the formula used above. Use the following formula to highlight the cells with mismatched data:

=$B3=$C3=FALSE

This formula will highlight the cells if a value in column B doesn't equal the adjacent value in column C.

Compare Two Columns & Highlight Matching Data

Method #3 – Compare Two Columns & Highlight Matching Data

Here's a different situation. Up until now, you have seen comparing two columns for row by row. Different types of data are put together differently and you may need to find if there are any matching items in two lists. What would be further helpful is if these matching values were highlighted. Time for Conditional Formatting to step in again.

For example, we will compare two lists of fruits from different vendors and highlight matching cells with Conditional Formatting. Find the steps below:

Here are the lists for comparison:

Compare Two Columns & Highlight Matching Data
  • Select the cells in the list.
  • Go to Home tab > Styles group > Conditional Formatting menu > Highlight Cell Rules > Duplicate Values.
Compare Two Columns & Highlight Matching Data
  • In the dialog box that has opened, choose the format for highlighting the cells from the drop menu. You can also go for a custom format.
Compare Two Columns & Highlight Matching Data
  • When done, click on OK.
Compare Two Columns & Highlight Matching Data

The matching values in both columns will be highlighted in the set format:

Compare Two Columns & Highlight Matching Data

Method #4 – Compare Two Columns & Highlight Mismatching Data

The exact flip on the situation above would be to highlight the values in both columns that do not match. The method is the same as the one we've just seen with one small change in the setting.

Using Conditional Formatting, we will highlight unique values in two columns with the steps below:

  • Select the values for comparison in both columns.
  • Go to the Conditional Formatting menu in the Styles group in the Home Then select Highlight Cell Rules options and Duplicate Values.
Compare Two Columns & Highlight Matching Data
  • In the drop menu reading Duplicate, select Unique.
  • In the second drop menu, choose the format you want the mismatched cells highlighted in.
Compare Two Columns & Highlight Mismatching Data
  • When done, click on the OK

As per the chosen format, the mismatched or unique cells from both columns will be highlighted:

Compare Two Columns & Highlight Mismatching Data

Method #5 – Compare Two Columns & Pull Matches (Exact Match)

If you were to search for some information from a tiringly long database, you best not opt to do it manually. For example, we have a bunch of event management companies and need to jot down their contact from a vast list of details of event management companies. Let's see such an example, fairly scaled-down, to give you an idea of how this works.

Compare Two Columns & Pull Matches (Exact Match)

In the second table, we have some event management companies and require their contact numbers to be filled. The contact numbers are to be pulled from the first table.

The tool we will use to find the information we require is the VLOOKUP function. The VLOOKUP function looks for a value in a column and returns the value in the same row, from another column. This is the formula we are using for our example:

=VLOOKUP(E3,$B$3:$C$12,2,FALSE)

To begin with, we are searching for the contact for the company mentioned in cell E3. The VLOOKUP function is to locate the company in the first column mentioned in the range B3:C12. It is important to lock the range as absolute references otherwise the reference will keep changing as the formula is copied down.

The "2" in the formula is the number of the column in the mentioned range from which the value is to be pulled. FALSE is used to find an exact match of E3 in column B.

VLOOKUP found an exact match of E3 in row 12 of column B. The corresponding contact was pulled from column C and returned.

Compare Two Columns & Pull Matches (Exact Match)

In case the company names mentioned in column E weren't exact copies of the names mentioned in column B, the formula would return a #N/A error. To make the formula more relenting of small mistakes like an extra space, missing space, a negligible typo, or a forgotten word, change the FALSE in the formula to TRUE. With TRUE in the formula, VLOOKUP will search for an approximate match with the lookup value.

Method #6 – Compare Two Columns & Pull Matches (Partial Match)

If the values you want to look up are vague, TRUE in the VLOOKUP function won't cover up for it and won't return the correct information. Don't worry, you're still not stuck. There's a little addition that can save you right. See the formula below for comparing two columns and pulling values using partial matches:

=VLOOKUP("*"&E3&"*",$B$3:$C$12,2,FALSE)

The formula has been copy-pasted from the example earlier with an addition to the lookup value i.e. E3. E3 has been enclosed in & operators and asterisks "*". Let's say we can't remember whether the companies have caterers or event management attached to their names. If we can remember at least one word unique to the company name, the asterisk in the formula makes up for the missing characters before and after E3. The & operators add the asterisks to E3.

In the first instance, VLOOKUP is to search for "Appetit" in column B. Cell C4 shows "Bon Appetit Catering". The asterisks have made up for the missing "Bon" from the start of the lookup value and "Catering" from the end of the lookup value.

Having found the partial match of "Appetit" in B4, VLOOKUP pulled the value from column 2 of the range B3:C12 and returned the contact number.

Compare Two Columns & Pull Matches (Partial Match)

Compared to all that learning, it's time for a break. That was all from us on comparing two columns in Excel. Hopefully, with our range of scenarios, you'll find how to match what and where, or how to not match it, if that's what you're aiming for. While you're finding the right fit for your problem, we'll be up to all good cracking the Excel enigmas down for you to decipher all its complexities.

About Mehwish Javaid

Mehwish, an ACCA-qualified professional, transitioned from an audit trainee to an Excel specialist. With a foundation in financial auditing, her 4+ years of Excel expertise, showcased as a Content Specialist at ExcelTrick, bridges her auditing background with advanced spreadsheet skills. Read more...