How to find values that appear in both of two lists
✓ Verified in LibreOffice 25.8.7.3Compare two columns and pull out the values they have in common.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0) | Excel 365/2021. Per-row alternative: =IF(COUNTIF($C$2:$C$4,A2)>0,"In both",""). |
| Google Sheets | =FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0) | Identical. |
| LibreOffice Calc | =FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0) | FILTER needs LibreOffice 24.8+; the per-row IF+COUNTIF form works in any version. |
How it works
COUNTIF(C2:C4,A2:A6) counts how many times each value from the first list appears in the second — an array of 0s and 1s — and FILTER keeps the first-list values whose count is positive. "banana" and "fig" are in both lists; "grape" is only in the second, so it doesn't appear. Swap the ranges to get the second list's matches, or use COUNTIF(...)=0 to find values in one list but NOT the other.
Verified, not just documented
We ran =FILTER(A2:A6,COUNTIF(C2:C4,A2:A6)>0) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned banana, fig — exactly the expected result. Every formula here is confirmed by actually executing it.