Compare two lists in Google Sheets

What you will build

Compare incoming customer IDs with an approved reference list and label each one as “Found” or “Missing”. The lesson uses a small fictional dataset so every step can be checked directly in Google Sheets.

Learning objective

You will use COUNTIF inside IF to test whether an ID appears in a reference list, lock the lookup range with absolute references, fill the formula down, and verify that the results recalculate when source data changes.

Prepare the sample data

Paste the tab-separated data below starting at A1. Column A is Incoming ID, column C is Approved ID, and column B is intentionally blank so the two lists remain visually separate.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Sample data in A1:C5 in the Google Sheets _result workbook at the start of the current audit, before the Status column is added.

Work through the example

Step 1: Keep the incoming list in A2:A5 and the approved list in C2:C4. Enter the header Status in D1.

Step 2: Select D2 and enter the formula below. It counts how many times A2 appears in C2:C4; if the count is zero it returns “Missing”, otherwise it returns “Found”.

Step 3: Fill the formula from D2 through D5. With the original sample, the results must be C01 = Found, C04 = Missing, C02 = Found, and C05 = Missing.

Formula to try

=IF(COUNTIF($C$2:$C$4,A2)=0,"Missing","Found")

Cell D2 selected in Google Sheets with the IF and COUNTIF formula visible in the formula bar and the source data visible in columns A:C.

How this formula works=IF(COUNTIF($C$2:$C$4,A2)=0,"Missing","Found")

Overall meaning of the whole formula
- Check whether the value in A2 appears anywhere in C2:C4; return "Missing" when there are zero matches and "Found" when at least one match exists.
IF(logical_expression, value_if_true, [value_if_false])
- `logical_expression` is required; it accepts an expression that evaluates to TRUE or FALSE. In this lesson the argument passed is `COUNTIF($C$2:$C$4,A2)=0`.
- `value_if_true` is required; it accepts the value or expression returned when the test is TRUE. In this lesson the argument passed is `"Missing"`.
- `value_if_false` is optional; it accepts the value or expression returned when the test is FALSE. In this lesson the argument passed is `"Found"`.
COUNTIF(range, criterion)
- `range` is required; it accepts the range to test. In this lesson the argument passed is `$C$2:$C$4`.
- `criterion` is required; it accepts a value, cell reference, or condition text to count. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- Sheets evaluates the innermost function calls first, then passes their results outward through the enclosing expression.
- COUNTIF($C$2:$C$4,A2) counts cells in the Approved ID list that match the value in A2.
- $C$2:$C$4 is an absolute reference, so the approved-list range does not shift when the formula is filled down.
- A2 is relative; when filled down it becomes A3, A4, and A5.
- =0 tests whether the count contains no matches.
- IF returns “Missing” when the count is zero and “Found” when at least one match exists.
Why the displayed final result appears
- IF returns “Missing” when the count is zero and “Found” when at least one match exists.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Incoming ID	Status
C01	Found
C04	Missing
C02	Found
C05	Missing

The original Approved ID list is C01, C02, C03. The current audit also verified recalculation: changing C4 from C03 to C04 changes C04 from Missing to Found; restoring C4 to C03 returns it to Missing.

Final Google Sheets results after restoring the sample: C01 Found, C04 Missing, C02 Found, and C05 Missing.

Common mistakes and fixes

If an ID looks identical but is still marked Missing, check for extra spaces or hidden characters. COUNTIF is not case-sensitive, so letter-case differences alone are usually not the cause.

Independent practice

Change C4 from C03 to C04 and predict the result before checking the Status column: the C04 row should change from Missing to Found. Then restore C4 to C03 so the workbook returns to the original sample.

Key takeaways

✓COUNTIF can test whether a value appears in a reference list.
✓IF converts the count into readable labels such as Found and Missing.
✓Lock the Approved ID range with absolute references before filling the formula down.
✓Keep A2 relative so each row checks its own Incoming ID.
✓Change one source value and restore it to prove the formula really recalculates.

Continue learning

Previous lesson: Track overdue tasks; Next lesson: Lookup values across tabs.

What you will build

Learning objective

Prepare the sample data

Paste the tab-separated data below starting at A1. Column A is Incoming ID, column C is Approved ID, and column B is intentionally blank so the two lists remain visually separate.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Work through the example

Step 1: Keep the incoming list in A2:A5 and the approved list in C2:C4. Enter the header Status in D1.

Step 2: Select D2 and enter the formula below. It counts how many times A2 appears in C2:C4; if the count is zero it returns “Missing”, otherwise it returns “Found”.

Step 3: Fill the formula from D2 through D5. With the original sample, the results must be C01 = Found, C04 = Missing, C02 = Found, and C05 = Missing.

Formula to try

=IF(COUNTIF($C$2:$C$4,A2)=0,"Missing","Found")

How this formula works=IF(COUNTIF($C$2:$C$4,A2)=0,"Missing","Found")

Overall meaning of the whole formula
- Check whether the value in A2 appears anywhere in C2:C4; return "Missing" when there are zero matches and "Found" when at least one match exists.
IF(logical_expression, value_if_true, [value_if_false])
- `logical_expression` is required; it accepts an expression that evaluates to TRUE or FALSE. In this lesson the argument passed is `COUNTIF($C$2:$C$4,A2)=0`.
- `value_if_true` is required; it accepts the value or expression returned when the test is TRUE. In this lesson the argument passed is `"Missing"`.
- `value_if_false` is optional; it accepts the value or expression returned when the test is FALSE. In this lesson the argument passed is `"Found"`.
COUNTIF(range, criterion)
- `range` is required; it accepts the range to test. In this lesson the argument passed is `$C$2:$C$4`.
- `criterion` is required; it accepts a value, cell reference, or condition text to count. In this lesson the argument passed is `A2`.
Whole-expression evaluation
- Sheets evaluates the innermost function calls first, then passes their results outward through the enclosing expression.
- COUNTIF($C$2:$C$4,A2) counts cells in the Approved ID list that match the value in A2.
- $C$2:$C$4 is an absolute reference, so the approved-list range does not shift when the formula is filled down.
- A2 is relative; when filled down it becomes A3, A4, and A5.
- =0 tests whether the count contains no matches.
- IF returns “Missing” when the count is zero and “Found” when at least one match exists.
Why the displayed final result appears
- IF returns “Missing” when the count is zero and “Found” when at least one match exists.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Incoming ID	Status
C01	Found
C04	Missing
C02	Found
C05	Missing

Common mistakes and fixes

If an ID looks identical but is still marked Missing, check for extra spaces or hidden characters. COUNTIF is not case-sensitive, so letter-case differences alone are usually not the cause.

Independent practice

Key takeaways

✓COUNTIF can test whether a value appears in a reference list.
✓IF converts the count into readable labels such as Found and Missing.
✓Lock the Approved ID range with absolute references before filling the formula down.
✓Keep A2 relative so each row checks its own Incoming ID.
✓Change one source value and restore it to prove the formula really recalculates.

Continue learning

Previous lesson: Track overdue tasks; Next lesson: Lookup values across tabs.

Compare two lists

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Compare two lists

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments