Find Duplicates Between Two Columns in Google Sheets (Matches and Missing Values)

To find the values that appear in two lists, ask for each value in the first list whether the second list contains it. COUNTIF and MATCH both do that, FILTER turns the answer into a list, and conditional formatting turns it into colors. The harder part is deciding what counts as the same value: in our test, case and a single space changed which items matched.

The two lists, in F2:F9 and G2:G8:

F: List 1 G: List 2
Apple banana
Banana Cherry
Cherry Elder
Date Fig
Fig Kiwi
Grape Lemon
“Kiwi ” (a space at the end) Mango
lemon

Tested in Google Sheets. Every formula on this page was run on November 14, 2026, and the results below are the ones Google Sheets returned. How we test

Flag each value: is it in the other list?

In H2, copied down to H9:

=COUNTIF($G$2:$G$8, F2)>0

It returned TRUE for Banana, Cherry, Fig and lemon, and FALSE for Apple, Date, Grape and “Kiwi “. =ISNUMBER(MATCH(F2, $G$2:$G$8, 0)) returned exactly the same. The second list is locked with dollar signs so it doesn’t move as you copy the formula down.

For a readable label instead of TRUE or FALSE:

=IF(COUNTIF($G$2:$G$8, F2), "Both", "Only in List 1")

COUNTIF returns 0 when there’s no match, and IF treats 0 as false and any other number as true. One catch: copied below the end of List 1, the formula labeled the empty rows “Only in List 1” too. Stop at the last value, or wrap it in IF(F2="", "", …).

Case and spaces change the answer

Formula in H2 Matches found
=COUNTIF($G$2:$G$8, F2)>0 Banana, Cherry, Fig, lemon
=SUMPRODUCT(EXACT($G$2:$G$8, F2)*1)>0 Cherry, Fig
=COUNTIF($G$2:$G$8, TRIM(F2))>0 Banana, Cherry, Fig, Kiwi, lemon

Found in testing: COUNTIF and MATCH treated “Banana” and “banana”, and “lemon” and “Lemon”, as the same value, but missed “Kiwi ” against “Kiwi” because of one trailing space. EXACT, which compares case, found only Cherry and Fig. TRIM around the value found Kiwi as well. Decide which of these is the right answer for your data before you trust a count of matches.

List the values in both columns

FILTER returns every value of List 1 whose COUNTIF in List 2 isn’t 0:

=FILTER(F2:F9, COUNTIF(G2:G8, F2:F9))

It returned Banana, Cherry, Fig and lemon, one under the other. Here COUNTIF receives the whole list F2:F9 as its condition and returns one count per value, and FILTER keeps the rows where the count is above 0. No ARRAYFORMULA was needed inside FILTER.

  • Ignoring stray spaces: =FILTER(F2:F9, COUNTIF(G2:G8, TRIM(F2:F9))) returned 5 values, “Kiwi ” included, as it’s written in List 1.
  • Case-sensitive: =FILTER(F2:F9, MAP(F2:F9, LAMBDA(x, SUMPRODUCT(EXACT(G2:G8, x)*1)))>0) returned Cherry and Fig.
  • Just the count: =SUMPRODUCT(COUNTIF(G2:G8, F2:F9)>0) returned 4.

List the values missing from the other column

You want Formula Result
In List 1, not in List 2 =FILTER(F2:F9, COUNTIF(G2:G8, F2:F9)=0) Apple, Date, Grape, “Kiwi “
The same with MATCH =FILTER(F2:F9, ISNA(MATCH(F2:F9, G2:G8, 0))) Apple, Date, Grape, “Kiwi “
In List 2, not in List 1 =FILTER(G2:G8, COUNTIF(F2:F9, G2:G8)=0) Elder, Kiwi, Mango

The two directions give different lists, so run both if you’re reconciling two exports. Kiwi shows up as missing on both sides because of the trailing space, which is often the first sign that one list needs cleaning. If no value is missing, FILTER returns #N/A; our guide to #N/A shows how to replace it.

Highlight the matches

Select F2:F9, open Format > Conditional formatting, choose Custom formula is and enter:

=COUNTIF($G$2:$G$8, F2)>0

In our test sheet, this colored F3, F4, F6 and F9: Banana, Cherry, Fig and lemon. To color the values that are not in the other list, use =COUNTIF($G$2:$G$8, F2)=0, and add F2<>"" with AND if the range runs past the end of the list: empty cells aren’t in List 2 either, which is why the label formula above marked them “Only in List 1”. More rules for duplicates inside one list are in how to highlight duplicates.

Combine the two lists without duplicates

=UNIQUE({F2:F9; G2:G8})

The braces with a semicolon stack the two ranges into one column. The result had 13 values, and =COUNTUNIQUE(F2:F9, G2:G8) also returned 13. Both kept Banana and banana, Lemon and lemon, and Kiwi and “Kiwi ” as separate values, because UNIQUE compares case and spaces. Data cleanup’s Remove duplicates handles case differently; see remove duplicates: menu vs UNIQUE.

In a spreadsheet set to a European locale, the array separators differ; our formula checker converts them.

Related guides