How to Compare Two Columns in Excel With a Formula

Formulas & functions · Updated November 18, 2026

To compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This labels each pair of cells as equal or different. If you need to find whether a value in one column appears anywhere in the other, use a lookup-style formula instead. Before starting, decide which kind of comparison you need and whether blanks or capitalization should affect the result.

What You’ll Need

  • An Excel workbook with the two columns you want to compare.
  • A blank column for results, such as column C.
  • A clear understanding of whether rows correspond to each other or whether you are checking for matches anywhere in the other list.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. The basic formulas also work in many older Excel versions. Excel for Mac has the same formula behavior; keyboard shortcuts are noted where they differ.

Step-by-Step

  1. Check how your data is arranged. For a row-by-row comparison, put the first list in column A and the second list in column B, with corresponding values on the same row. For example, A2 and B2 might contain the same customer’s name from two different exports. Keep the headings in row 1 and begin the comparison on row 2. If one list is longer, plan to fill the result formula down through the last used row in either list so that you do not overlook extra entries.
  2. Choose a blank results column. Click C1 and enter a heading such as Comparison. Click C2 for the first result. Keeping the results in a separate column makes it easier to filter, sort, or review the outcome without changing the original values. Avoid putting the formula in either source column, because doing so would overwrite data you may need to check.
  3. Compare the cells directly. With the first list in A2:A100 and the second list in B2:B100, enter this in C2 to return TRUE when the two cells are equal and FALSE when they are not: =A2=B2. This is the simplest option when a logical result is useful for another formula or filter. Excel compares the values in the same row, not the overall contents of both columns. For example, if A2 and B2 both contain North, the result is TRUE; if B2 contains South, the result is FALSE.
  4. Use readable labels instead of TRUE and FALSE. With the same layout—values in A2:A100 and B2:B100, and the result in C2—enter =IF(A2=B2,"Match","Different"). The formula checks the two cells in that row and returns Match or Different. If A2 contains 25 and B2 contains 25, the result is Match. If B2 contains 30, the result is Different. You can change those two text labels to something that suits your worksheet, such as Same and Check, while keeping the quotation marks around the text.
  5. Fill the formula down the results column. Select C2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last data row. Excel adjusts the row references as it fills, so the next line compares A3 with B3, then A4 with B4, and so on. You can also select C2 through the final result cell and choose Home > Fill > Down. On Windows, Ctrl+D fills down; on Mac, Command+D is the usual equivalent. Check the first and last few results to confirm the formula reached the intended rows.
  6. Find values that occur anywhere in the other column. This is different from comparing pairs on the same row. With the value to search for in A2 and the comparison list in B2:B100, enter this in C2: =IF(COUNTIF($B$2:$B$100,A2)>0,"Found in B","Not found"). The dollar signs keep the search range fixed when you fill down, while A2 changes to A3, A4, and so on. If A2 contains a name that appears in B17, the result is Found in B even if the two names are on different rows. COUNTIF is not case-sensitive, so capitalization differences do not prevent a match.
  7. Decide how to handle capitalization and blank cells. A standard equality comparison treats text with different capitalization as equal. If capitalization must match, compare the paired values with EXACT. With values in A2:A100 and B2:B100, enter =IF(EXACT(A2,B2),"Match","Different") in C2 and fill down; North and NORTH will be marked Different. If two empty cells should be identified separately from matching values, use the following version in C2 with the same column layout: =IF(AND(A2="",B2=""),"Both blank",IF(A2=B2,"Match","Different")). It labels two empty cells Both blank, while a value in only one cell is Different.

For a visual check rather than a written result, you can also highlight different paired cells with Conditional Formatting. With the comparison cells in A2:B100 and the range A2:B100 selected, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$A2<>$B2, choose a format, and confirm. The dollar signs lock the columns while the row number changes for each selected row. This highlights both cells in a row when they differ; it does not create a text result in column C.

Common Mistakes

Using a row-by-row formula for two unsorted lists. A2=B2 answers whether those two particular cells match. It does not tell you whether A2 appears elsewhere in column B. If the lists are in a different order, use the COUNTIF method to search the other list, or sort both lists carefully before comparing paired rows. Sorting just one column can break the relationship between rows and produce misleading results.

Comparing different kinds of values. A number stored as text may look identical to a number stored as a numeric value but behave differently in some comparisons or calculations. Dates can also appear different because of cell formatting even when their underlying values are equal. Check the formula bar and cell formats if a result seems wrong. Do not assume that a displayed appearance proves the stored values are identical.

Forgetting to extend the formula. A correct formula in C2 only checks row 2. If the list continues to row 100 but the formula stops at row 50, the remaining rows have no result. After filling down, scroll to the bottom of the data and make sure the results column reaches the final row. A filter on the result column can help you review all Different outcomes together.

Misreading blanks or capitalization. Basic equality comparisons are not case-sensitive, and two empty cells can appear to match. That behavior may be right for your task, but it can conceal missing information or capitalization differences. Choose the EXACT version when case matters, and use the blank-aware version when two empty cells need a distinct label.

Assuming a match means a unique match. The COUNTIF formula reports that a value appears at least once in the other range. It does not count how many times it appears or identify a single corresponding row. If duplicates matter, use a count-based check or inspect the matching values separately rather than treating Found in B as proof of a one-to-one match.

Tips

Keep an untouched copy of the source data before cleaning or sorting it. If the result column is long, turn the range into an Excel table with Insert > Table. A formula entered in a table column generally fills through the table, and new rows can inherit the calculated-column formula. Check the table’s first and last entries after adding data, especially if the worksheet contains manually entered values or formulas.

Use a consistent range size for list searches. In the example, the fixed search range ends at B100, so values added in B101 or below are not included. Update the range when the list grows, or use a table column as the search range for a table-based workbook. For ordinary paired comparisons, relative references are useful because they advance one row at a time. For a lookup across a fixed list, the dollar signs in the range prevent it shifting as the formula is copied.

To review only differences, select the results column and use Data > Filter, then filter for Different. For the direct TRUE/FALSE version, filter for FALSE. This does not change the source cells and lets you focus on the rows that need attention. When you finish, clear the filter to restore the full view. If you share the workbook, leave the labels or headings clear so another person can tell whether the results are row-by-row comparisons or list-membership checks.

Troubleshooting

The formula appears as text instead of calculating. The cell may have been formatted as Text before the formula was entered, or the entry may begin with an apostrophe. Change the cell format to General using Home > Number Format, edit the cell, and press Enter. If needed, delete and re-enter the formula. Also check that there is no space or apostrophe before the equals sign.

Every row says Different even though values look alike. Check for leading or trailing spaces, nonprinting characters, or mismatched data types. Spaces copied from websites or reports can make names differ even when they look the same at a glance. Inspect the source cells in the formula bar and clean the data before comparing. If you normalize values, keep a copy of the originals so the cleanup is reversible. Do not remove spaces automatically if they could be meaningful in your data.

The match formula misses a value you can see in the list. Make sure the search range includes the row containing that value and that the formula uses the intended column. With COUNTIF, wildcard characters such as an asterisk or question mark in the search value can be treated as pattern characters rather than ordinary literal text. If your data includes those characters and must be compared literally, use a different exact-match approach or clean and verify the source values first.

The formula result does not update after an edit. Check that calculation is set to Automatic under Formulas > Calculation Options. If the workbook is in Manual calculation mode, press F9 on Windows to recalculate; on Mac, use the recalculation shortcut shown for your Excel installation, or choose Formulas > Calculate Now. Also confirm that the formula refers to the cells you edited, rather than a different row or sheet.

Some rows have no result. Look for gaps in the filled formula or for rows outside the range you selected. Select the first correct formula cell and fill it down again through the data. If the source columns have different lengths, decide whether extra values should be labeled as unmatched, and extend the formulas to the longer column’s last row.

Frequently Asked Questions

Can I compare two columns without creating a helper column?
Yes. Select a range and use Conditional Formatting with a comparison rule to highlight different paired cells. A helper column is still useful when you need to filter, count, or export the results as text.

Does Excel treat uppercase and lowercase letters as different?
Not in a standard equality comparison or COUNTIF search. Use the EXACT function for a case-sensitive row-by-row comparison. Remember that EXACT checks the two cells in the same row; it is not by itself a search across a whole list.

How can I compare two columns when their values are in a different order?
Use a membership check that searches each value from one column anywhere in the other, such as the COUNTIF example above. This identifies whether a value appears in the other list, regardless of row position. It does not confirm that duplicate counts are equal or pair each occurrence with a unique counterpart.

Can the formula show only the values that do not match?
The IF formula can label rows Different while returning Match for equal pairs. Filter the result column to show only Different. If you want to display the differing source values themselves, you can use the labels to locate those rows and inspect columns A and B.

Will these formulas work in Excel for Mac or Excel for the web?
The basic comparison, IF, EXACT, and COUNTIF functions work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon labels and keyboard shortcuts can vary slightly by platform, but the formulas use the same comma-separated syntax in standard regional settings. In some locales, Excel may require semicolons instead of commas.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.