Compare Two Columns in Excel: Find Matches and Differences

"Which customers are on list A but not list B?" "Do these two price columns match?" There are two different questions here: comparing row by row, and comparing lists. Each needs a different formula.

Row by row: are they the same?

=A2=B2

TRUE or FALSE. With labels:

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

Case-sensitive version: =EXACT(A2,B2).

For numbers with a tolerance (rounding differences):

=IF(ABS(A2-B2)<0.01,"Match","Different")

Lists: is this item anywhere in the other column?

Is A2 anywhere in column B?

=IF(COUNTIF($B$2:$B$500,A2)>0,"In both","Only in A")

Lock the range with $ before filling down. See COUNTIF.

Or with MATCH:

=ISNUMBER(MATCH(A2,$B$2:$B$500,0))

Pull the matching value

If the item exists in the other list, bring back its price:

=XLOOKUP(A2,$D$2:$D$500,$E$2:$E$500,"Missing")

Then compare: =IF(B2=XLOOKUP(...),"Same price","Price changed"). See XLOOKUP.

Items in A but not in B (Microsoft 365)

=FILTER(A2:A500,COUNTIF(B2:B500,A2:A500)=0)

Returns the full list of missing items. See FILTER.

Highlight differences visually

Select both columns, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and switch to "Unique" to highlight items that appear only once.

Clean first

Spaces and number/text mismatches make identical items look different. Run TRIM on both lists first.

Frequently asked questions

How do I compare two columns for matches in Excel?

Row by row: =A2=B2. As lists: =COUNTIF($B$2:$B$500,A2)>0.

How do I find values in one column that are not in another?

=IF(COUNTIF($B$2:$B$500,A2)=0,"Missing",""), or FILTER in Microsoft 365.

How do I compare two columns and return a value?

Use XLOOKUP or VLOOKUP to fetch the matching value from the other list.

Why do matching values show as different?

Usually hidden spaces or one value stored as text and the other as a number.