To find values missing from a second list in Google Sheets, count each value in the comparison list and label the zero matches. This leaves both original lists visible and makes the comparison easy to inspect row by row.

Our example compares requested item IDs with received IDs. A repeated request stays repeated: the method flags missing request rows rather than silently removing duplicates or assuming that one receipt fulfills every quantity requested.

Two ID lists with Missing and Present labels and a count of three missing rows.
The helper column flags three missing request rows; repeated IDs are still separate rows.Credit: MathBlog

Put the two lists beside each other

Copy this tab-separated block into A1 on a blank worksheet:

Source-entry preview: A1:B7 (formulas shown as entered)
Row A B
1 Requested Received
2 N101 N102
3 N102 N104
4 N103 N106
5 N104
6 N103
7

Copy only the tab-separated block below. Worksheet letters and row numbers in the preview are display labels, not part of the data.

Requested	Received
N101	N102
N102	N104
N103	N106
N104	
N103	
	

Requested IDs occupy A2:A6, with an additional blank cell in A7. Received IDs occupy B2:B4. Blank positions in the tab-separated block are intentionally empty; they are not the word “blank.”

Type Status in C1. Each result in C will describe the request in the same row. The comparison range is fixed at B2:B4 so it does not shift when the formula is copied downward.

Flag each requested ID

Enter this in C2, then copy it down through C7:

=IF(A2="","",IF(COUNTIF($B$2:$B$4,A2)=0,"Missing","Present"))

The outer IF leaves a blank request unlabeled. For other rows, COUNTIF counts appearances of the current ID in the received list. A count of zero produces Missing; any positive count produces Present.

Expected results in C2:C7
Requested ID Expected status
N101 Missing
N102 Present
N103 Missing
N104 Present
N103 Missing
(blank) (empty result)

The dollar signs in $B$2:$B$4 keep the comparison list fixed. The A2 reference moves to A3, A4, and so on, so each request is checked separately.

Count the missing rows

In F2, enter:

=COUNTIF(C2:C7,"Missing")

The expected answer is 3. N101 is missing once, and N103 is missing on two request rows. There are only two distinct missing IDs, but this formula counts the three labeled rows.

That difference matters when your original list contains duplicates. If the report asks “How many different products are missing?”, first define a distinct-ID calculation. If it asks “How many requests remain unmatched?”, keeping the original rows may be appropriate.

Check membership rather than quantity

This approach asks whether an ID exists anywhere in the received list. Suppose N102 was requested on three rows but received once. All three requests would show Present, because each finds the same received ID.

It therefore does not reconcile quantities, match individual shipment lines, or allocate receipts to requests. Those tasks need a transaction identifier or a quantity comparison. Decide what one record means before calling a membership flag an inventory reconciliation.

Account for matching rules

COUNTIF text matching is not case sensitive. If your system treats lowercase and uppercase IDs as different, choose a case-sensitive method instead. The fictional IDs here also contain no wildcard characters. A question mark or asterisk in a real ID can be interpreted as a pattern, so literal wildcard characters need deliberate escaping.

Leading or trailing spaces can prevent a label from matching the intended value. Inspect and clean both lists consistently, while keeping the raw columns available. A cleanup rule applied to only one list can create another mismatch.

Keep the source boundaries current

If you append more received IDs, extend the fixed comparison range. If you append request rows, copy the status formula to those rows and include them in the missing-row count. A formula cannot report records outside its input range.

To validate the finished sheet, locate one known match, one known absence, the repeated N103 request, and the blank row. A correct comparison should explain all four cases without changing the original lists.

Further reading