Compare Two Columns in Excel: Matches and Differences

TL;DR: This guide teaches you how to compare two Excel columns using formulas, Conditional Formatting, or functions like XLOOKUP and XMATCH.

Introduction

Comparing values in Excel is part of everyday work, whether you are checking reports, cleaning data, or verifying lists from different sources. At some point, you will need to check whether two columns match, find missing values, or quickly spot differences. Knowing how to compare two columns in Excel helps you do these checks accurately without wasting time on manual comparisons.

Here are some of the ways you can compare two columns in Excel:

• Compare values row by row to see if they match
• Find values that exist in one column but not in the other
• Highlight duplicates or differences automatically
• Use formulas and built-in Excel features to speed up the process

In this article, you will see quick ways to compare 2 columns in Excel and understand which method fits your situation. You will also learn how to spot matches, missing values, and duplicates with minimal effort.

Quick tip: to compare two columns in Excel: Type =A2=B2 in a new column to compare two columns in Excel and get TRUE or FALSE for matching values. To find values that appear in one column but not in the other, use MATCH or COUNTIF formulas.

Join The Ranks of Top-Notch Data Analysts!Data Analyst CourseExplore Program

Which Method Should You Use? (Match vs Differences vs Duplicates)

There are three main ways to compare two columns in Excel. If you are unsure which one to use, the table below breaks it down clearly based on what you want to check:

Compare Two Columns Row-by-Row (A2 vs B2)

So far, you have seen the different methods to compare two columns in Excel for matches, differences, and duplicates. Now, let’s start with the simplest approach and compare two columns row by row, such as checking A2 against B2.

Step 1: Open your Excel sheet and make sure the two columns you want to compare are side by side, for example, Columns A and B, starting from row 2.

Step 2: Click cell C2, which will display the comparison result for A2 and B2.

Step 3: Enter the formula =A2=B2 in cell C2 and press Enter.

Step 4: Excel displays TRUE if the value in A2 matches B2, or FALSE if they differ.

Step 5: Select cell C2, move your cursor to the small square at the bottom-right corner of the cell, and drag it down to apply the formula to the remaining rows.

Step 6: Review the results column to quickly identify which rows match and which do not.

Compare Two Columns for Matches Across Lists (A Exists in B)

After checking values row by row, you may need to know whether a value from one column appears anywhere in another column, even if the order is different. This type of comparison is useful when working with two separate lists. Here is how you can do it step by step.

Step 1: Put the list you want to check in Column A, and the reference list in Column B. Start both lists from row 2, and keep the headers in row 1.

Step 2: Click on cell C2, which will display whether the value in A2 exists in Column B.

Step 3: Enter the following formula in cell C2:

=COUNTIF(B:B, A2)>0

Then press Enter.

Step 4: If Excel shows TRUE, it means the value in A2 is somewhere in Column B. If it shows FALSE, that value isn’t in Column B.

Step 5: Click on cell C2, then grab the little square at the corner and drag it down to fill the formula for the rest of Column A.

Step 6: Scan the results in Column C to quickly identify which values from Column A are found in Column B and which are missing.

Join The Ranks of Top-Notch Data Analysts!Data Analyst CourseExplore Program

Compare Two Columns for Differences (Find Missing Values)

Once matches are identified, the next question is usually about missing values. This method helps you find entries that exist in one column but not in the other. Let’s look at the steps to compare 2 columns in Excel for differences.

Step 1: Place the main list in Column A and the comparison list in Column B, with headers in row 1.

Step 2: Click on cell C2, where the result will appear while you compare two columns in Excel for differences.

Step 3: Enter the following formula in C2:

=IF(COUNTIF(B:B, A2)=0, "Missing", "Present")

Press Enter.

Step 4: If the result shows Missing, the value in Column A does not exist in Column B. If it shows Present, the value exists, making it easy to compare columns in Excel for differences.

Step 5: Drag the fill handle down to apply the formula to the remaining rows.

Step 6: Filter Column C by Missing to quickly see all values that are not found in Column B.

Highlight Duplicates Across Two Columns Using Conditional Formatting

If you want Excel to visually compare the data, conditional formatting is a practical option. It highlights matching or duplicate values automatically without complex formulas. Here are the steps to apply this method.

Step 1: Select both columns you want to compare.

Step 2: Go to the Home tab and click Conditional Formatting.

Step 3: Choose Highlight Cells Rules and then click Duplicate Values.

Step 4: In the dialog box, keep Duplicate selected and choose a highlight color.

Step 5: Click OK. Excel will instantly highlight values that appear in both columns.

Step 6: Look over the highlighted cells to see which ones match or show duplicates between the two columns.

Compare Two Columns Using XLOOKUP / XMATCH (Excel 365)

After using basic formulas and formatting, you can move to newer Excel functions for cleaner comparisons. XLOOKUP and XMATCH are especially useful when working with larger datasets. Here is how you can use them step by step.

Steps Using XLOOKUP

Step 1: Enter your primary list in Column A and the lookup list in Column B.

Step 2: Click on cell C2.

Step 3: Enter the formula:

=XLOOKUP(A2, B:B, B:B, "Not Found")

Press Enter.

Step 4: If the value exists in Column B, Excel returns the matched value. If not, it returns Not Found.

Step 5: Drag the formula down to apply it to other rows.

Steps Using XMATCH (Alternative)

Step 1: Click on cell C2.

Step 2: Enter the formula:

=XMATCH(A2, B:B)

Press Enter.

Step 3: If a number appears, the value exists in Column B. If #N/A appears, the value is missing.

Step 4: Copy the formula down for the rest of the column.

If you’re using an older version of Excel, you can try the MATCH function to compare two columns. For example: =MATCH(A2, B:B, 0)

Our Data Analyst Course will help you learn analytics tools and techniques to become a Data Analyst expert! It's the perfect course for you to jumpstart your career. Enroll now!

Key Takeaways 

• Excel gives a few easy ways to compare two columns, whether you’re checking for exact matches, missing values, or duplicates
• Simple formulas like =A2=B2, COUNTIF, and MATCH handle most everyday comparisons without any hassle
• Conditional Formatting helps you quickly see duplicates, which is great when you’re working with large spreadsheets
• Functions like XLOOKUP and XMATCH make comparisons smoother and easier to manage in large or complex datasets

Additional Resources

• Must Know Excel Shortcuts
• MS Excel Interview Questions
• Excel Tutorial for Beginners
• How to Calculate Percentage in Excel

FAQs

1. How do I compare two columns in Excel for a match?

Use a helper column with the formula =A2=B2. Excel checks values in both columns for each row and returns TRUE for matches and FALSE for mismatches.

2. How do I compare two columns in Excel for differences?

To spot differences, look at one column and check whether the values appear in the other using COUNTIF or MATCH. If something is missing, highlight it so it’s easy to see.

3. How do I highlight duplicates across two columns in Excel?

Select both columns and apply Conditional Formatting > Duplicate Values. Excel highlights values that appear in both columns, making it easy to compare them.

4. What is the best Excel formula to compare two columns?

There is no single best option. The Excel formula to compare two columns depends on your goal, whether you want matches, differences, or returned values.

5. How do I return “Match/No Match” when comparing columns?

Use an IF formula like =IF(A2=B2,"Match","No Match") to clearly label comparison results instead of TRUE or FALSE.

6. How do I compare two lists in Excel and find missing values?

Use COUNTIF or MATCH to check one list against another and filter results marked as missing. Excel commonly uses this approach to compare lists during data validation.

7. How do I compare two columns and return only the common values?

Use XLOOKUP to search for values in one column in another and return only those that appear in both lists.

8. How do I compare columns using XLOOKUP?

XLOOKUP searches for a value in one column and returns the matched value or a custom message if no match is found.

9. How do I compare two columns using MATCH or COUNTIF?

MATCH returns a position number when a value exists, while COUNTIF confirms presence with a count. Both methods are useful for matching two columns across lists in Excel.

10. How do I compare text values, ignoring extra spaces or case?

You can use TRIM to remove extra spaces. Then, depending on whether you care about case, you can check matches with a regular comparison or the EXACT function.

11. How do I compare two columns row-by-row (A2 vs B2)?

Place the columns side by side and use =A2=B2 to check each row individually. This is useful when order matters.

12. How do I compare two columns and mark duplicates by color?

Conditional Formatting lets you automatically highlight duplicate values, helping you review repeated entries more quickly.

13. How do I compare two columns in Excel for unique values?

You can use the UNIQUE function or the Remove Duplicates option to pull out values that show up just once across both columns.

14. How do I compare two columns in Google Sheets vs Excel?

Both tools can use similar formulas, but Excel gives you extra options when you’re working with bigger datasets or more complicated tasks.

15. What’s the fastest method for large datasets (Power Query)?

Power Query efficiently compares large tables by processing data in batches rather than relying on row-by-row formulas.