Match Excel Formula for Two Columns: Compare Values

Formulas & functions · Updated November 18, 2026

To compare two cells on the same row in Excel, enter =IF(A2=B2,"Match","Different") in C2 and fill it down. If you mean checking whether each value in one column appears anywhere in the other, use a lookup or count formula instead; the right choice depends on whether row position matters.

First decide what “match two columns” means

There are two common comparisons, and they answer different questions. A row-by-row comparison asks whether the value in A2 equals the value in B2, then whether A3 equals B3, and so on. A list-membership comparison asks whether a value from one column occurs anywhere in the other column, regardless of its row. Choose the method before writing a formula, or a correct formula may answer the wrong question.

For example, suppose column A contains an employee ID and column B contains the ID entered on a separate form. If the rows represent the same employee, compare each row directly. If A is a list of requested IDs and B is a master list, search the whole master list for each requested ID. The first method is sensitive to row order; the second is not.

  • Same-row comparison: Use when corresponding records are already aligned, such as two versions of a price list.
  • Find a value anywhere: Use when the lists may be sorted differently or have different lengths.
  • Compare whole lists: Check both directions if you need to know whether the lists contain the same values, not just whether every item in one list appears in the other.

Compare two columns row by row

With the values to compare in A2 and B2, enter the formula below in C2. It displays a readable result, which makes it easy to filter the comparison column for differences.

With the values to compare in A2 and B2, enter in C2: =IF(A2=B2,"Match","Different").

For example, if A2 contains 125 and B2 contains 125, C2 returns “Match.” If B2 contains 130 instead, it returns “Different.” Select C2 and drag the fill handle down, or copy and paste the formula into the remaining rows. Excel adjusts the relative references as it fills: the next row compares A3 with B3.

If you only need a TRUE or FALSE result, use a direct comparison. With the values in A2 and B2, enter in C2: =A2=B2. TRUE means Excel considers the two cell values equal; FALSE means they differ. This version is useful in later calculations or filters, while the IF version is easier for a report reader to interpret.

Excel’s ordinary equality comparison does not distinguish uppercase from lowercase text: “west” and “WEST” compare as equal. To require the same capitalization, use EXACT. With text values in A2 and B2, enter in C2: =EXACT(A2,B2). EXACT returns TRUE only when the text matches character for character, including case. It is intended for text comparisons; for general mixed data, consider whether case sensitivity is actually part of the requirement.

Check whether a value appears anywhere in another column

When the lists are not aligned, a row-by-row comparison is misleading. Instead, test each value against the full range in the other column. COUNTIF is a familiar option and works in older Excel versions as well as Microsoft 365.

With the list to check in A2:A100 and the search list in B2:B100, enter this in C2 and fill down: =IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing").

The dollar signs keep the search range fixed as the formula is copied, while A2 changes to A3, A4, and so on. If A2 contains an ID found in B2:B100, the result is “Found”; if it is absent, the result is “Missing.” For instance, if A2 is 204 and that ID appears in B17, the formula still returns “Found.”

Another option is MATCH, which returns a position when it finds a value. With the value to search in A2 and the list in B2:B100, enter in C2: =IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"Found","Missing"). The final 0 requests an exact match. MATCH’s position is relative to the search range: if the value is in B17, the returned position is 16 because B2 is the first item in that range. ISNUMBER turns that position-or-error result into a simple check.

Microsoft 365 and Excel 2021 also have XLOOKUP. With the value to search in A2 and the lookup list in B2:B100, enter in C2: =IF(ISNA(XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100)),"Missing","Found"). XLOOKUP returns the matching item when it finds one and an error when it does not; ISNA checks for the not-found error. For a quick single lookup, XLOOKUP can also return a custom not-found message, but the IF form here makes the two outcomes explicit.

Compare both lists, not just one direction

Finding every A value in B does not prove the lists are identical. B may contain additional values that never appear in A. To identify all differences, check A against B and then check B against A. If neither direction produces a missing item, the lists have the same distinct values, subject to how duplicates are handled.

With items in A2:A100 and B2:B100, put this in C2 and fill down to identify entries from A absent from B: =IF(COUNTIF($B$2:$B$100,A2)=0,"Missing from B","Present in B"). For the reverse check, with the same ranges, put this in D2 and fill down: =IF(COUNTIF($A$2:$A$100,B2)=0,"Missing from A","Present in A"). Filter columns C and D for “Missing” to review the differences.

This checks whether each value is represented at least once, rather than whether the lists contain the same number of repetitions. If A contains ID 204 twice and B contains it once, both A entries will still be reported as present. That is often right for checking membership, but it is not a full duplicate-count comparison.

Understand duplicates and exactness

COUNTIF and MATCH answer whether an item can be found; they do not pair up repeated occurrences. For example, if one column contains the same product code three times and the other contains it once, a membership formula marks all three as found. To compare counts, count how many times each value appears in each list.

With the value in A2 and the comparison lists in A2:A100 and B2:B100, enter in C2: =COUNTIF($A$2:$A$100,A2)=COUNTIF($B$2:$B$100,A2). TRUE means that value occurs the same number of times in each range. This is a per-value check; to establish that the full lists have identical contents including duplicates, apply it to the distinct set of values from both lists or otherwise ensure every distinct value is checked in both directions.

Also check whether the data types match. A number stored as a number and the same-looking digits stored as text can behave differently in lookups or comparisons. Leading or trailing spaces can make two text entries appear identical on screen while they differ in the underlying cell contents. Imported data is a common source of these inconsistencies.

COUNTIF criteria treat the asterisk and question mark as wildcard characters. If a cell being searched may literally contain those characters, a COUNTIF test can interpret them as patterns rather than ordinary text. For most ID, name, and numeric lists this is not an issue, but it matters for codes containing symbols. If the data is messy, inspect representative values and decide whether spaces, case, data type, and wildcard characters should count as meaningful differences.

Highlight matches or differences visually

A formula result is useful when you want a filterable status column. Conditional formatting is better when you want to spot results directly in the original data. On Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. On Mac, the Conditional Formatting commands are also on the Home tab, though the dialog layout may differ slightly.

For a row-by-row comparison, select A2:B100 and create a rule using the following formula. With the selected range beginning at A2 and the paired values in columns A and B, enter: =$A2<>$B2. Choose a formatting style, then confirm the rule. The relative row number lets the rule evaluate each selected row; the dollar signs keep the comparison in columns A and B. This highlights both cells in each row where the entries differ.

For list membership, select A2:A100 and use a formula-based rule that checks whether each selected value appears in B2:B100. With the selection starting at A2 and the comparison list in B2:B100, use: =COUNTIF($B$2:$B$100,A2)=0. Set a fill color to flag values absent from B. To flag missing values in the opposite direction, create a second rule on B2:B100 with the ranges reversed. Confirm that the “Applies to” range matches the cells you selected; an incorrect range can make a valid formula highlight the wrong rows.

Common problems and how to fix them

  • Everything says Different: Confirm that the formula is comparing the intended rows and that the data types match. Check for extra spaces or one column containing text-formatted numbers.
  • Values appear Found when they should not: Check for duplicates, partial-looking codes, and wildcard characters in COUNTIF criteria. Verify a few results manually.
  • Only the first row works: Fill the formula down and check that the lookup range uses dollar signs so it does not shift. The value being checked should usually remain a relative reference.
  • Blank cells show as matches: Two blank cells compare equal. If blank rows should be ignored, wrap the comparison in an IF test. With the row’s comparison values in A2 and B2, enter in C2: =IF(AND(A2="",B2=""),"",IF(A2=B2,"Match","Different")). This returns a blank only when both cells are blank; a blank paired with a nonblank value is still marked different.
  • Some rows are missing from the check: Extend the formula’s range to cover all data rows, or use an Excel Table so formulas and references can expand as records are added.

For a large workbook, avoid checking thousands of completely unused rows unless necessary. Use the actual data range, or convert a data range to a table with Ctrl+T on Windows (Control+T on Mac). Tables make structured data easier to manage, and calculated-column formulas generally fill down as new table rows are added.

Which formula should you choose?

Use the simplest method that matches the question. For aligned records, direct equality is clear and fast. For checking whether a value exists in a separate list, COUNTIF is widely compatible and easy to read; MATCH is useful when you also need the found position. Use XLOOKUP if your Excel version supports it and you want a flexible lookup with an explicit not-found result. Use conditional formatting when visual review matters more than producing a separate status column.

Before relying on the outcome, decide how the comparison should treat case, duplicates, blanks, extra spaces, and numbers stored as text. These are not cosmetic details: they define what “same” means for the task. If the results will drive an important import, reconciliation, or report, test the formula on a few known matches and nonmatches, including at least one edge case such as a duplicate or a blank.

Frequently Asked Questions

Can Excel compare two columns and return only matching rows?
Yes. First identify matches with a helper formula such as COUNTIF or XLOOKUP, then filter the result column to show only “Found” or “Match.” Excel 2021 and Microsoft 365 also support FILTER for returning matching rows dynamically, but a helper column is often simpler to audit and works in more versions.

Does Excel’s equals sign distinguish uppercase and lowercase?
No. A standard comparison treats uppercase and lowercase text as equal. Use EXACT when capitalization must match; it returns TRUE only for text with the same characters and case.

How do I find values in column A that are not in column B?
Use a membership check for each A value against the full B range, then filter for the missing result. A two-way check is needed if you also want to find values present in B but absent from A.

Why does my comparison say two identical-looking values differ?
Look for leading or trailing spaces, text-versus-number differences, hidden characters from imported data, and capitalization if using a case-sensitive formula. The formula compares stored values, not just how the cells look when formatted.

Can I compare columns with different numbers of rows?
Yes. For membership checks, use the full range of the list being searched, even if it is longer or shorter than the source list. For a row-by-row check, only compare rows that represent corresponding records; unmatched extra rows need a separate check.

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.