Matching two columns covers three jobs: check whether a value exists in the other list, pull the matching value across, or highlight what's missing or changed — using COUNTIF, XLOOKUP/INDEX-MATCH, or a highlight rule. Or just describe the two columns to ExcelGPT and get the exact match formula back.
Do it with AI insteadRow-by-row match
Use =IF(A2=B2, "Match", "Different") and fill down.
Is it in the other list?
Use =COUNTIF(B:B, A2)>0 to test whether A2 exists in column B.
Pull the matching value across
Use =XLOOKUP(A2, B:B, C:C, "Not found") — or =INDEX(C:C, MATCH(A2, B:B, 0)) on older Excel — to return the value matched in another column.
Highlight differences
Select both columns ▸ Conditional Formatting ▸ Highlight Cells ▸ Duplicate/Unique.
=XLOOKUP(A2, B:B, C:C, "Not found")Use =IF(COUNTIF(B:B,A2)=0, "Missing", "").
Use XLOOKUP or INDEX/MATCH to pull the value across: =XLOOKUP(A2, B:B, C:C). COUNTIF only tells you if it exists.