To compare two columns in Google Sheets, you usually want one of three answers: which items appear in both, which are only in column A, and which are only in column B. This page gives a formula for each, plus a way to highlight the matches.
Column A (A2:A6): Apple, Banana, Cherry, Date, Elderberry
Column B (B2:B5): banana, Fig, Date, Grape
Row 1 holds the headers. Note that "banana" in B is lower case. Matching in these formulas ignores case, so it still counts as a match.
In C2, fill down:
=IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,"In both","Only in A"))
COUNTIF counts how many times the value in A2 appears in column B. If the count is more than zero, it exists in both. The empty-cell check stops blank rows from being labelled.
Result on the sample: Apple is Only in A, Banana is In both, Cherry is Only in A, Date is In both, Elderberry is Only in A.
MATCH does the same job and some people find it easier to read:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A"))
It gives the same result. The 0 means exact match. If the lookup fails, MATCH returns an error, and ISNUMBER turns that into FALSE.
Instead of filling down, use a single formula in C2. The sheet fills it for you:
=ARRAYFORMULA(IF(A2:A6="","",IF(COUNTIF(B2:B6,A2:A6)>0,"In both","Only in A")))
Use a range that covers your data. Pointing it at an entire column such as A2:A also works, but it will be slower.
=IFERROR(FILTER(A2:A6, A2:A6<>"", COUNTIF(B2:B6, A2:A6)=0), "None")
Returns Apple, Cherry, Elderberry, one per row.
=IFERROR(FILTER(B2:B5, B2:B5<>"", COUNTIF(A2:A6, B2:B5)=0), "None")
Returns Fig and Grape.
=IFERROR(FILTER(A2:A6, A2:A6<>"", COUNTIF(B2:B6, A2:A6)>0), "None")
Returns Banana and Date. IFERROR is there because FILTER gives an error when it finds nothing, and "None" is a friendlier result.
If your lists grow, extend the ranges, for example to A2:A1000. Keep the ranges the same size inside each pair.
Sometimes you do not want to search the whole list. You want to know whether A2 equals B2 on the same row, for example when comparing an old and new version of the same sheet.
=A2=B2
This returns TRUE or FALSE and ignores case. For a case-sensitive check, use:
=EXACT(A2,B2)
Try it with these rows. A2:A4 is Red, Blue, Green and B2:B4 is red, Blue, Teal. The first formula gives TRUE, TRUE, FALSE. EXACT gives FALSE, TRUE, FALSE, because "Red" and "red" differ in case.
Select A2:A100, open Format > Conditional formatting, choose "Custom formula is", and enter:
=COUNTIF($B$2:$B$100,$A2)>0
Every value in column A that also appears in column B is coloured. On the sample, Banana and Date are highlighted. To highlight the values that are only in A, change the test to =AND($A2<>"",COUNTIF($B$2:$B$100,$A2)=0). To colour column B instead, select B2:B100 and swap the references: =COUNTIF($A$2:$A$100,$B2)>0.
The dollar signs matter. $B$2:$B$100 keeps the lookup range fixed, and $A2 lets the row move as the rule is applied down the column.
Most "it should have matched" problems come from three things.
Extra spaces. "Date " is not the same as "Date". Run Data > Data cleanup > Trim whitespace on both columns first.
Numbers stored as text. A cell showing 1001 might be text in one column and a number in the other. They will not match. Convert both to the same type.
Small spelling differences. "Jon Smith" and "John Smith" are different strings to every formula above. Formulas compare exactly, so near-misses need fuzzy matching.
Disclosure: Snakbacks, the publisher of this site, makes two free add-ons for Google Sheets.
Duplicate Remover: Remove Duplicates in Sheets finds repeated rows within a sheet on the columns you choose, ignoring case and extra spaces, and can highlight, mark, copy out or delete them. It is built for duplicates inside one list, not for comparing two separate lists. If you stack both lists into one column, it can highlight the values that appear twice, which are the ones in both. For a straight A versus B comparison, the formulas above are the right tool.
Fuzzy Match: Fuzzy VLOOKUP for Sheets is for the near-misses. FUZZYSCORE(A2, B2) gives a similarity from 0 to 1, and FUZZYMATCH returns the closest value from a clean list, so "Acme Pty. Ltd" can find "ACME Pty Ltd" even though the text differs.
Duplicate Remover is coming soon to the Google Workspace Marketplace. Fuzzy Match is coming soon to the Google Workspace Marketplace.
To compare two lists where the same item is spelled differently, Snakbacks also sells Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. FUZZYMATCH can return the closest value from list B for each item in list A.
Need to catch near-duplicates that exact formulas miss, such as "Acme Pty Ltd" and "ACME Pty. Ltd"? The Data Cleanup Kit for Google Sheets (US$15, no add-on install) groups them for you with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text: https://snakback.gumroad.com/l/data-cleanup-kit
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.