Excel Compare Two Columns for Matches: Formulas and Examples

To compare two columns in Excel for matches, use a formula such as =IF(COUNTIF($B$2:$B$6,A2)>0,"Match","No match").

The result tells you whether each value in column A appears anywhere in column B, and the method you choose depends on whether you need labels, matching values, or a visual highlight.

Choose the comparison you need

“Compare two columns” can describe several different tasks.

First decide what the result should show:

Check whether each value in one column appears in another: use COUNTIF or MATCH.
Return a value from the matching row: use XLOOKUP or INDEX and MATCH.
Find values shared by both columns: use a match check, then filter for matches.
Compare cells on the same row: use a direct comparison such as =A2=B2.

For the formulas below, assume the first list is in A2:A6 and the second is in B2:B6.

Put formulas in a separate result column so the original data stays intact.

Check whether a value appears anywhere in the other column

In C2, enter this formula and fill it down alongside the values in column A:

=IF(COUNTIF($B$2:$B$6,A2)>0,"Match","No match")

COUNTIF counts how many times the value in A2 occurs in the fixed range B2:B6.

If the count is greater than zero, IF returns “Match”; otherwise, it returns “No match.” The dollar signs keep the lookup range fixed as you copy the formula down, while A2 changes to A3, A4, and so on.

For example, if column A contains Red, Blue, and Green, while column B contains Green, Red, and Yellow, the checks return “Match” for Red and Green and “No match” for Blue.

If you prefer a Boolean result, use =COUNTIF($B$2:$B$6,A2)>0.

It returns TRUE when a match exists and FALSE otherwise.

Excel's comparison operator is the greater-than sign in the formula text; write it as text inside the <code> element, not as an HTML tag.

Use MATCH for a position check

MATCH can test whether a value is present and can also report its position within the lookup range.

For an exact match, enter:

=IF(ISNUMBER(MATCH(A2,$B$2:$B$6,0)),"Match","No match")

The final argument, 0, requests an exact match.

Without it, MATCH may use an approximate match depending on the other arguments and the sort order of the data.

Use exact matching for ordinary text or ID comparisons.

To return the position when there is a match, use =MATCH(A2,$B$2:$B$6,0).

If Excel cannot find the value, it returns #N/A.

You can display a friendlier result with =IFNA(MATCH(A2,$B$2:$B$6,0),"Not found").

Return a related value from the matching row

If the second list has associated information, such as a product name in column B and a price in column C, use XLOOKUP to return the price for a value in A2:

=XLOOKUP(A2,$B$2:$B$6,$C$2:$C$6,"Not found",0)

This searches for A2 in B2:B6 and returns the corresponding item from C2:C6.

The “Not found” argument controls the result when no exact match exists, and 0 specifies exact matching.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016.

For older Excel versions, use =IFERROR(INDEX($C$2:$C$6,MATCH(A2,$B$2:$B$6,0)),"Not found").

This combines INDEX, which returns a value from column C, with MATCH, which finds the corresponding row within the lookup range.

Compare cells on the same row

If each value in column A should be compared only with the cell beside it in column B, use a direct equality check in C2:

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

This is different from searching the whole of column B: it compares only A2 with B2.

To show a Boolean result instead, enter =A2=B2.

Excel's standard text comparison is not case-sensitive, so Green and green are treated as equal by this formula.

Highlight matching values without adding a formula column

Conditional Formatting can highlight entries that occur in the other list.

To highlight values in A2:A6 that also appear in B2:B6:

Select A2:A6.
On the Home tab, select Conditional Formatting, then New Rule.
Choose the option to use a formula to determine which cells to format.
Enter =COUNTIF($B$2:$B$6,A2)>0.
Choose a format and confirm the rule.

The formula uses A2 as a relative reference, so Excel checks each selected cell in turn.

To highlight values in column B that also occur in column A, apply a second rule to B2:B6 and use =COUNTIF($A$2:$A$6,B2)>0.

Handle blanks, duplicates, and case sensitivity

Blank cells can produce confusing results because an empty lookup value may be counted as a match when the other range contains blanks.

If empty cells should remain unlabelled, add a blank check:

=IF(A2="","",IF(COUNTIF($B$2:$B$6,A2)>0,"Match","No match"))

COUNTIF reports whether a value occurs, not how many distinct matching rows exist.

If a value appears several times in column B, the result is still simply “Match.” To count its occurrences, use =COUNTIF($B$2:$B$6,A2).

Most standard Excel lookup and equality formulas ignore letter case.

For a case-sensitive comparison against the entire second list, use SUMPRODUCT with EXACT:

=SUMPRODUCT(--EXACT(A2,$B$2:$B$6))>0

EXACT distinguishes uppercase and lowercase letters, while SUMPRODUCT checks the results across the range.

This formula returns TRUE if at least one case-sensitive match exists.

Make the formulas work with changing data

If the lists will grow, consider converting each range into an Excel Table with Ctrl+T.

Structured references, such as =COUNTIF(Table2[Value],[@Value])>0, can expand as new table rows are added.

Replace Table2 and Value with your actual table and column names.

For a simple worksheet, fixed ranges such as $B$2:$B$6 are easy to understand.

For a large or frequently changing list, tables reduce the chance that new entries fall outside the lookup range.

If a formula returns an unexpected result, check for extra spaces, inconsistent data types such as text numbers versus numeric values, and spelling differences.

The TRIM function can remove many leading or trailing spaces, but it does not fix every source of inconsistent data.