How to compare two columns in Excel: 11 Best Methods

Need to compare two columns in Excel and highlight the differences, matches, or missing values? Whether you’re working with product lists, data entries, or reports, Excel offers several powerful ways to compare columns(cell to cell and cell to column) and clean up your data.

This step-by-step guide explores 11 powerful methods to compare two columns in Excel — all techniques I frequently use in my daily workflow. We’ll start with simple comparisons using the equals sign (=) and the IF() function, then move on to advanced tools like VLOOKUP(), COUNTIF(), EXACT(), array formulas, conditional formatting, and even VBA macros. Plus, we’ll dive into modern Excel 365/2021 functions like XLOOKUP() and FILTER(), which make comparing columns easier, faster, and more dynamic than ever.

Also, I have included screenshots and flowchart images with each column comparison method for your easy and quick learning.

By the end, you’ll know exactly which method to use based on your needs—and how to spot duplicates, highlight mismatches, and streamline your spreadsheet workflow. This guide will show you how to match columns within the same sheet or between two different sheets.

Compare Two Columns for Matches Using Formulas

Excel has many formulas to compare two columns. Moreover, using formulas to compare 2 columns is one of the easiest ways to find matches.

Specifically, we can use IF and EXACT functions, the (=) operator, and VLOOKUP for column data comparison.

For instance, I have two columns with the fruit names as below. In particular, two values like Apple and Grapes are identical in both columns.

Example:

Let’s see how to compare both these columns using different functions and formulas.

A. Compare two columns in Excel using the IF() Function

I want to compare columns A and B so I can use the IF() function as below.

=IF(A2=B2, "Match", "No Match")

This formula will compare cells A2 and B2 and return Match if the values are the same, otherwise, it will return No Match.

You can, therefore, drag the same formula in the remaining rows in order to find identical values.

B. Compare two columns in Excel using the Equals Operator (=) (Return TRUE or FALSE)

For simple comparison, you can use the equals (=) operator:

=A2=B2

This formula, therefore, returns TRUE if the values are the same; however, it returns FALSE if they do not match.

So it will return TRUE if:

• First, A2 has “Banana” and B2 has “Banana”.
• Additionally, A2 has “Cherry” and B2 has “cherry”. (non-case-sensitive)

But it will return FALSE if:

• For instance, A2 has “Mango” and B2 has “Apple.

Moreover, see the image below.

Now, you might be thinking that the IF() function and the (=) operator work the same in comparison. However, what is the difference between them?

Here are the key differences between the IF() function and the (=) operator when using them for value comparison.

Difference Between IF Formula and = Operator While Comparing in Excel

When to use the Equal (=) operator and IF Formula

In short, for quick comparisons where TRUE/FALSE is enough, you can use the = operator, and the IF formula is better if you need a detailed output or additional logic.

The IF() function and (=) operator are not case-sensitive.

So, if you compare “Apple” with “apple,” using the IF function, it will return a match. And if you use the (=) operator, it will return TRUE.

If you want to compare cell values with case-sensitivity, then you can use the EXACT function.

Let’s see how the EXACT function works.

C. Compare two columns in Excel using the EXACT() With Case Sensitivity

Assume we have two cells (A2 and B2) with the names of fruits (i.e., Apple and apple).

Let’s apply an EXACT() function to check how it works.

=EXACT(A2, B2)

The comparison result is as below.

Here you can see in the image above that it has returned false because “Apple” and “apple” do not match with the case-sensitivity check.

Now, if you change the word from “apple” to “Apple” in B2 cell, it will, consequently, return true, as shown in the image given below.

Now, you can apply the same formula (Using Excel’s Fill a formula down into adjacent cells) in all the remaining rows; consequently, this will allow you to compare them with case-sensitivity.

Note: The EXACT function returns TRUE only if both values are identical, including case sensitivity.

D. Using VLOOKUP() to Compare Values from Different Sheets

Sometimes, the data you need to compare is spread across multiple sheets. In such cases, therefore, VLOOKUP() is a powerful function that helps you find matches between columns on different sheets efficiently.

Don’t know how to use VLOOKUP for comparison? Well, don’t worry. I am here to help you.

Using VLOOKUP, you can compare cells(i.e., Sheet1 A2 cell) to cells(i.e., Sheet2 B2 cell) and cells(i.e., Sheet1 A2 cell) to columns(i.e., Sheet2 B column).

Cell-to-cell comparison from two different sheets using VLOOKUP()

Let’s consider that I have Column A in Sheet 1 and Column B in Sheet 2 to compare.

Example:

To compare values from cells from two different sheets and get the result in the B column on Sheet 1, use the following formula:

=IF(ISNA(VLOOKUP(A2, Sheet2!B2, 1, FALSE)), "No Match", "Match")

It will return Match if the values of the A2 cell from sheet1 and the B2 cell from sheet2 match. Otherwise, it will return No Match. See image below for more clarity.

Cell to column comparison from 2 different sheets using VLOOKUP

Next, if you want to compare specific cells’ values(i.e., A2) from sheet 1 with specific column’s value(i.e., B) from sheet 2, then you can use the formula given below.

=IF(ISNA(VLOOKUP(A2, Sheet2!B:B, 1, FALSE)), "No Match", "Match")

This formula will return Match if it finds the A2 cell’s(Sheet 1) value anywhere in the B column(Sheet 2).

Look at the image below.

Do you know how this VLOOKUP formula will work? Let me explain it.

How This Formula Works:

1. VLOOKUP(A2, Sheet2!B:B, 1, FALSE) – This function searches for the value in A2 within column B of Sheet2. If the value exists, it returns the corresponding value; otherwise, it results in an error (#N/A).
2. ISNA(…) – This checks if VLOOKUP returns an error, meaning the value is missing from column B in Sheet2.
3. IF(…, “No Match”, “Match”) – If an error is detected (meaning there is no match), the function returns “No Match”; otherwise, it returns “Match”.

Why Use VLOOKUP for Column Comparison?

Do you know why you should use VLOOKUP to compare columns from different sheets?

Reasons are:

• Efficiency: Indeed, it allows you to quickly check whether a value from one sheet exists in another without manually scanning through large datasets.
• Cross-Sheet Comparison: Furthermore, it works across multiple sheets, making it ideal for comparing data from different sources.
• Dynamic Updates: Moreover, as new data is added, the formula updates automatically, ensuring an accurate comparison at all times.

E. Compare two columns in Excel using the COUNTIF() Formula

Another way to compare values across sheets is by using the COUNTIF() function.

This is the best alternative to VLOOKUP, as you can use it to match a specific cell from Sheet 1 with a specific cell from Sheet 2 or a whole column from two different sheets.

Match cell with cell using IF() and COUNTIF()

The COUNTIF formula to compare cell with cell from two different sheets is:

=IF(COUNTIF(Sheet2!B2, A2) > 0, "Match", "No Match")

This formula will differentiate the values of the A2 cell from Sheet 1 and the B2 cell from Sheet 2.

You can change the cell and column as you need in the above formula.

Match cell with column using IF() and COUNTIF() function

If you want to compare a specific cell’s value from Sheet 1 with the whole column from Sheet 2 using COUNTIF, then you can use the formula given below.

=IF(COUNTIF(Sheet2!B:B, A2) > 0, "Match", "No Match")

This formula checks how many times the value in A2 appears in column B of Sheet2. If the count is greater than 0, it means the value exists; otherwise, it does not.

In the image above, you can see that it shows Match text for matching values and No Match text for those values which are not match.

F. Compare using Array Formulas

Array formulas are a powerful way to work with multiple values at once. You can use them to compare entire columns for matches, especially in older versions of Excel that do not support dynamic arrays.

For example, I want to find values in Column A that are also in Column B. I can use the formula given below.

=IF(SUM(IF(A1=B$1:B$100,1,0))>0,"Match","No Match")

Steps to use the formula:

• After typing the formula in the C1 cell, press Ctrl + Shift + Enter (for Excel 2016 and earlier).
• Excel will enclose the formula in curly braces {}, indicating it’s an array formula.
• This formula checks if the value in A1 appears anywhere in the range B1:B100.

You can apply the same formula in the remaining cells using the fill handle to get the comparison result as image below.

Compare Two Columns with Conditional Formatting

You can use Excel’s conditional formatting feature to highlight matches or differences visually.

If you don’t know how to compare two columns in Excel using conditional formatting, I will show you with examples.

A. Highlight Matching Values

To highlight matching values using conditional formatting:

• Select both columns (e.g., A and B).
• Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

• Choose a formatting style(i.e., Duplicate and Red text from the drop-downs) and click the OK button.

• It will compare and highlight text in Red color which are same in both columns.
• In our example, CPU, Mouse, Printer, and USB Cable appear in both columns. It will highlight all of them.

B. Highlight Differences

Also, you can highlight & differentiate differences between 2 columns using conditional formatting.

To highlight differences, you can follow the same steps that we followed to highlight matching values, except select unique in the dropdown instead of duplicate.

When you click on the OK button, it will cross-check column values and highlight differences as shown in the image given below.

This way, you can compare and highlight matches and differences using the conditional formatting in Excel.

You can read my article on how to remove duplicates in Excel. If you want to remove duplicates after highlighting matching values,

Compare two columns in Excel using Find & Select

If you do not want to use the formula for comparison, Use Excel’s built-in Find & Select tool to visually analyze the differences between two columns.

To use it, you can follow the steps given below to compare columns A and B.

Steps:

• Select columns A and B.
• Go to Home > Find & Select > Go To Special.

• In the Go to Special dialog box, choose Row Differences and click the OK button.

• When you click OK, Excel will highlight cells in Column A that are different from Column B, as shown in the image below.

Compare two columns in Excel using VBA Macro

If you don’t want to use formulas and tools for column comparison, you can use a VBA Macro to automate the compare columns task.

You can use the VBA code given below to compare the first 100 cells from columns A and B.

VBA Code to Compare Columns in Excel

Sub CompareColumns()
    Dim rngA As Range, rngB As Range, cell As Range
    Set rngA = Range("A1:A100")
    Set rngB = Range("B1:B100")
    For Each cell In rngA
        If cell.Value <> cell.Offset(0, 1).Value Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

You can insert this column comparison Macro code in Excel from the Developer tab > Visual Basic > Insert > Module and run it using the shortcut Alt + F8 > Select Macro > Run.

When you run the macro, it will compare the values of columns A with B and highlight differences in Yellow as shown in the image below.

How to compare two columns in Excel using advanced methods

In Excel 365 and Excel 2021, you can compare two columns efficiently using the advanced functions like FILTER() or XLOOKUP(). These functions help you find matches, differences, or missing values between two lists.

Use the FILTER() function to compare columns in excel

You can use Excel’s FILTER() function to compare and extract matching or non-matching values from two columns that meet a condition.

Let’s see how to use the FILTER() function to find matching and non-matching values

Find Matching Values

Let’s say you have two columns with computer device names.

You want to extract values from Column A that also exist in Column B.

Steps:

• Select a blank cell where you want the results (e.g., C2).
• Enter the following formula:

=FILTER(A2:A8, ISNUMBER(MATCH(A2:A8, B2:B8, 0)), "No Match")

• Press Enter.

Explanation:

• MATCH(A2:A8, B2:B8, 0): Checks if each value in Column A exists in Column B.
• ISNUMBER(…): Converts matches into TRUE and non-matches into FALSE.
• FILTER(A2:A6, …): Extracts only the matching values.
• “No Match”: Displays this message if no matches are found.

Result:

The function will return matching values given below.

CPU
Mouse
Printer
USB Cable

See the result in the Image below to find matching values.

Find Non-Matching Values

To find values in Column A that do NOT exist in Column B, use this formula:

=FILTER(A2:A8, ISNA(MATCH(A2:A8, B2:B8, 0)), "No Match")

This works similarly, but ISNA(…) identifies values that are not found in Column B.

Result:

It will return non-matching values given below.

Keyboard
Cable
SMPS

See the result in the image below to find non-matching values.

Compare Two Columns in Excel Using XLOOKUP()

One can use the XLOOKUP() function to return corresponding values and also return missing values.

Example: Identify Matching or Missing Values

You want to check whether values in Column A exist in Column B.

Steps:

• Select a blank cell next to A2 (e.g., C2).
• Enter the following formula:

=XLOOKUP(A2, B2:B8, B2:B8, "Not Found", 0)

• Press Enter and drag down to fill the column.

Explanation:

• A2: The value to search for.
• B2:B8: The range where Excel searches for A2.
• B2:B8: Returns the found value if there’s a match.
• “Not Found”: Displays this when no match is found.
• 0: Exact match mode.

Result:

This will return:

Final Thoughts

Comparing two columns in Excel is a common task, whether you’re working with lists, reports, or large datasets. The good news? Excel gives you plenty of ways to do it.

From basic techniques like using the equals operator (=), IF() function, and EXACT(), to more advanced tools like VLOOKUP(), COUNTIF(), array formulas, and conditional formatting, there’s a method for every skill level. And if you’re using Excel 365 or 2021, don’t miss out on the power of XLOOKUP and FILTER—modern functions that make column comparison faster and more flexible.

Aravind

Hi, I’m Aravind, a seasoned Automation Test Engineer with 17+ years of industry experience. I specialize in tools like Selenium, JMeter, Appium, and Excel automation. Through this blog, I share practical tutorials, tips, and real-world insights to help testers and developers sharpen their automation skills.