How to check if a value exists in a list
✓ Verified in LibreOffice 25.8.7.3Test whether a value appears anywhere in a column — membership, allow-lists, validation.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =COUNTIF(A2:A100,D2)>0 | Returns TRUE/FALSE. Wrap in IF for custom labels. |
| Google Sheets | =COUNTIF(A2:A100,D2)>0 | Identical. |
| LibreOffice Calc | =COUNTIF(A2:A100,D2)>0 | Identical. |
How it works
COUNTIF counts the matches and the >0 turns it into a clean TRUE/FALSE — "banana" is in the list. This beats the MATCH/ISNUMBER combination for readability and the two behave identically for exact values. Caveats worth knowing: COUNTIF is case-insensitive, and if the probe value contains * or ? they act as wildcards — use SUMPRODUCT(--EXACT(...)) when case or literal symbols matter.
Verified, not just documented
We ran =COUNTIF(A2:A4,D2)>0 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned True — exactly the expected result. Every formula here is confirmed by actually executing it.