Check If a Value Exists in a Range (COUNTIF & XMATCH Membership Test)

Check a literal ID

The initial query INV-104 is present in A4. XMATCH with match mode 0 treats * and ? literally. Matching is case-insensitive. This example treats an empty query as no match, even if the list contains empty cells.

Keep identifiers consistently typed. Spaces are significant; this formula does not clean imported IDs or validate the source roster.

=IF(E2="",FALSE,ISNUMBER(XMATCH(E2,A2:A6,0)))

When COUNTIF is appropriate

COUNTIF(A2:A6,E2)>0 works for ordinary IDs such as INV-104. COUNTIF criteria interpret * and ? as wildcards, ~ as an escape, and leading comparison operators as criteria. It is not a general literal-ID test.

Use the XMATCH version above when the identifier can contain those characters. A match reports presence, not stock quantity or permission to perform an action.

=COUNTIF(A2:A6,E2)>0

Check either watch-list ID

D2 and D3 are two independent candidates. OR returns TRUE when at least one nonblank candidate matches. Duplicate roster entries do not change this Boolean result. This explicit two-item example does not claim to implement an arbitrary-length watch list.

=OR(IF(D2="",FALSE,ISNUMBER(XMATCH(D2,A2:A6,0))),IF(D3="",FALSE,ISNUMBER(XMATCH(D3,A2:A6,0))))

Test before reusing

Try INV-999, a lowercase ID, a trailing space and the literal query *. Add an actual * entry to see the result change. Clear both watch-list cells to verify FALSE rather than matching a blank roster row.

Further reading

• Microsoft Support — COUNTIF function
• Microsoft Support — XMATCH function