Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. For each ID in the old list, test whether COUNTIF finds it anywhere in the new list, then reverse the test for new IDs. Row-by-row A2=B2 is not a list comparison.
Use the fictional practice file and independent answer check. Work in a copy or a permitted demo account.
Before you start. This practice compares exact ID strings in one Google Sheet. COUNTIF is case-insensitive and recognizes wildcard characters in criteria; the fictional IDs contain no wildcards and differ by number. If a real list has inconsistent spaces, capitalization requirements, or repeated IDs, normalize and decide duplicate handling separately before interpreting the result.
1. See why side-by-side is misleading
Import the fictional CSV into a private Google Sheet. Column A is the old list; column B is the new list in a different order. A simple A2=B2 comparison would flag PR-101 and PR-104 as different even though both appear somewhere in each list. We need membership anywhere in the opposite column, not equality on the same row.
Checkpoint: Five old and five new IDs; rows deliberately shuffled.
2. Check each old ID against the new list
In C1 type Old-list status. In C2 enter =IF(COUNTIF($B$2:$B$6,A2)=0,"Missing from new","Present") and fill through C6. The dollar signs keep the five-cell new-list range fixed as the old-list reference changes row by row. Google documents COUNTIF as a conditional count and IF as the branch that labels zero matches.
Checkpoint: C3 and C6 say Missing from new for PR-102 and PR-105.
3. Check each new ID against the old list
In D1 type New-list status. In D2 enter =IF(COUNTIF($A$2:$A$6,B2)=0,"New only","Present") and fill through D6. This reverse pass catches arrivals that a one-way check cannot show. PR-107 and PR-106 are new only, regardless of their row numbers.
Checkpoint: D4 and D6 say New only for PR-107 and PR-106.
4. Verify the common IDs
Read PR-101, PR-103, and PR-104 in both columns. Their status cells should say Present even though the IDs do not line up row for row. If one is unexpectedly missing, inspect for a pasted space or an edited ID before changing the formula. Do not silently delete an item to force a match.
Checkpoint: Three shared IDs; four exceptional IDs total.
5. Turn exceptions into a review list
Filter the status columns for Missing from new and New only, or copy those four IDs into a review note. The old-only IDs may represent omissions; the new-only IDs may represent legitimate new records. The formula identifies differences, not which list is correct. Investigate each exception against the source system before merging anything.
Checkpoint: Old-only PR-102 and PR-105; new-only PR-107 and PR-106.
6. Extend the range deliberately
For a larger real list, update both COUNTIF ranges to cover all filled IDs before relying on the statuses. The fixed ranges in this five-row exercise stop at row six; adding a sixth new ID without expanding them would make the check incomplete. Recount the input rows and spot-check a known shared ID after every range edit.
Checkpoint: Range endpoints match the actual data size.
Verify the result
The old list contains PR-101 through PR-105. The new list contains PR-104, PR-101, PR-107, PR-103, and PR-106 in a different order. Missing from new: PR-102 and PR-105. New only: PR-107 and PR-106. PR-101, PR-103, and PR-104 are present in both lists despite appearing on different rows. No source ID is deleted or overwritten.
If it still does not work
This practice compares exact ID strings in one Google Sheet. COUNTIF is case-insensitive and recognizes wildcard characters in criteria; the fictional IDs contain no wildcards and differ by number. If a real list has inconsistent spaces, capitalization requirements, or repeated IDs, normalize and decide duplicate handling separately before interpreting the result.
Official sources
- COUNTIF function in Google Sheets (checked 20 October 2026).
- IF function in Google Sheets (checked 20 October 2026).
Any video panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.