Guides / Google Sheets

Compare Two Columns in Google Sheets: Matches and Missing Values

To compare two lists in Google Sheets, use COUNTIF to check whether each item appears anywhere in the other column. Check both directions to find missing values. Use FILTER to collect those results, or conditional formatting to highlight matches.

Follow this example in Google Sheets on a computer. Working in Excel instead? Use our Excel column comparison guide.

Compare columns in the tool →

Same row or anywhere in the other column?

Use =A2=B2 when the two cells belong to the same record and their rows are already aligned. It returns TRUE or FALSE. For a comparison that also distinguishes uppercase and lowercase text, use =EXACT(A2,B2).

For two independently ordered lists, use the COUNTIF steps below. In our example, A2 contains Cedar and B2 contains Maple, so =A2=B2 returns FALSE. But Cedar appears in B4, so it belongs in the shared results. You do not need to sort the lists first.

Set up one small example

In a blank sheet, put List A in A1 and List B in B1. Enter these made-up tree names in A2:A7 and B2:B6. Leave the cells marked empty blank; do not type the word “empty”. Keep B7 blank too.

Expected distinct results: only in A: Pine and Birch; only in B: Oak and Elm; in both: Cedar and Maple. Cedar occurs twice in A. The formulas below show when repeated rows are kept and when they are removed.

Find values missing from either column

1. Check each item in A against B

1. Type A checked against B in C1.
2. Paste the formula below into C2 and press Enter.
3. Select C2:C7, then press Ctrl+D on Windows or ⌘+D on Mac to fill down. The formula copies to each row.

=IF(A2="","",IF(COUNTIF($B$2:$B$6,A2)=0,"Missing from B","Found in B"))

COUNTIF searches B2:B6 for the value in A2. A count of zero means the value is missing from B. The outer IF leaves the helper cell empty when the input is blank.

The $ signs keep B2:B6 fixed as the formula fills down. For example, C7 must check A7 against $B$2:$B$6, not against a range shifted five rows down.

2. Check each item in B against A

Type B checked against A in D1. Paste this formula into D2, press Enter, select D2:D6, then press Ctrl+D on Windows or ⌘+D on Mac to fill down.

=IF(B2="","",IF(COUNTIF($A$2:$A$7,B2)=0,"Missing from A","Found in A"))

The first check cannot find Oak or Elm: those values occur only in B. This second check finds them.

Scroll horizontally on a phone to see every column.

3. Show only the missing rows

1. Select A1:D7, including the headers. Choose Data → Create a filter.
2. Open the filter icon in C1. Under Filter by values, clear the selection, select only Missing from B, then click OK. Read column A: row 4 is Pine and row 7 is Birch.
3. Reset the C1 filter to include all values before checking D1. Filter D1 to Missing from A and read column B: row 3 is Oak and row 6 is Elm.
4. Choose Data → Remove filter before continuing.

Filters hide entire rows. These two lists are independent, so a visible value in B beside a missing value in A is not its matching record. On a shared sheet, use Data → Create filter view if the filtering should apply only to your view.

Extract missing values and common items

Keep the completed helper columns C and D. Put Only in A in E1, Only in B in F1 and In both in G1. Leave the cells below these headers empty so the results can expand downward. Enter each formula once in its starting cell; do not fill it down.

E2: items in A that are missing from B

=IFNA(FILTER(A2:A7,C2:C7="Missing from B"),"No missing values")

Expected output: E2 = Pine and E3 = Birch.

F2: items in B that are missing from A

=IFNA(FILTER(B2:B6,D2:D6="Missing from A"),"No missing values")

Expected output: F2 = Oak and F3 = Elm.

G2: distinct values found in both lists

=IFNA(UNIQUE(FILTER(A2:A7,C2:C7="Found in B")),"No shared values")

Expected output: G2 = Cedar and G3 = Maple. UNIQUE removes the repeated Cedar row. Without UNIQUE, the shared output from A would be Cedar, Maple, Cedar.

The two missing-value formulas keep repeated source rows. To return each missing value once too, wrap their FILTER expression in UNIQUE, as in the shared-value formula.

When no rows match, FILTER returns #N/A. IFNA replaces that with No missing values or No shared values. These are status messages, not list items. Keep each FILTER input and condition range the same length; while adjusting a formula, test FILTER on its own first, because IFNA can also hide other #N/A problems.

If Sheets reports that an array result could not expand, move the formula to a clear area or move the content below it. Do not overwrite source data to make room.

Highlight matches in Google Sheets

1. Select A2:A7.
2. Choose Format → Conditional formatting.
3. In the Single color panel, check that Apply to range is A2:A7.
4. Under Format cells if, choose Custom formula is and enter the formula below.
5. Choose a fill color under Formatting style, then click Done.

=AND(A2<>"",COUNTIF($B$2:$B$6,A2)>0)

A2, A3 and A5 should be highlighted: Cedar, Maple and Cedar. To highlight missing items instead, replace >0 with =0 in the same rule. Then only A4 and A7 should be highlighted. The blank A6 stays uncolored in either rule.

The relative reference A2 must match the first row of the formatting range. These instructions use two columns in the same sheet; cross-sheet conditional formatting needs a different reference setup.

Before using your own lists

• Ranges: replace the ending row numbers with your actual list lengths. Fill C to the end of A and D to the end of B. Update the FILTER ranges and formatting range too. Headers must stay outside the comparison ranges.
• Blank cells: the helper formulas skip blank inputs and formulas returning an empty string. A cell containing spaces is not empty. Check it before treating it as an item.
• Repeated values: COUNTIF checks whether at least one match exists. It does not reconcile quantities. Two Cedar rows in A and one in B still count as a shared value.
• Letter case: COUNTIF matches Cedar with cedar. If case identifies different items, this COUNTIF workflow is unsuitable. EXACT distinguishes case for the same-row check; the browser tool below also offers a case-sensitive list comparison.
• Spaces and hidden characters: text with an extra space can fail to match. The formulas do not normalize the source. Inspect unexpected results before cleaning either list; visually similar text may contain different characters.
• Special characters and numbers: COUNTIF interprets * and ? as wildcards, ~ as an escape, and can interpret comparison operators and numeric text as criteria. These simple examples are for ordinary text labels. For literal identifiers, preserve leading zeros as text at the source and check the matching rules before comparing.
• Formula separators: these examples use English function names and commas. If your spreadsheet's locale expects semicolons, replace the argument-separating commas with semicolons. Keep straight quotation marks around text.

Compare once without formulas

For a one-time membership check, you can copy each column into CompareTwoLists. The tool processes the lists in your browser. It compares values, rather than entire rows or quantities.

1. Copy A2:A7 into List A and B2:B6 into List B, one column at a time, without headers. Use cells without internal line breaks.
2. For this example, turn Trim outer whitespace off, turn Ignore case on and use Original order. This makes the tree-name example comparable with COUNTIF. The tool treats wildcard characters literally, so these settings do not reproduce every COUNTIF rule.
3. Choose Compare lists. Expect Only in A: Pine, Birch; Only in B: Oak, Elm; In both: Cedar, Maple. The tool groups repeated values and retains their source references.

For a case-sensitive comparison, leave Ignore case off. If you already have lists open in the tool, return to that original tab. See how matching and result exports work before changing the rules for your own data.