FIND vs SEARCH (vs MATCH): locating text the right way
FIND and SEARCH both return where a substring starts inside a text cell — they differ on exactly two things: case sensitivity and wildcards. MATCH sounds similar but answers a different question entirely: which ROW of a range holds a value.
The differences at a glance
| FIND | SEARCH | MATCH | |
|---|---|---|---|
| Looks inside | One text string | One text string | A range of cells |
| Returns | Character position | Character position | Position (row/column number) in the range |
| Case-sensitive | Yes — "Red" ≠ "red" | No | No (exact-match mode) |
| Wildcards (* ?) | No — treated literally | Yes | Yes (with match type 0) |
| Not found | #VALUE! | #VALUE! | #N/A |
| Compatibility | Universal | Universal | Universal — all verified in every version we test |
Which should you use?
- FIND — Position of a known, exact-case substring — part codes, case-sensitive IDs.
- SEARCH — The usual choice for "does this cell contain X" checks (with ISNUMBER) — case-insensitive and wildcard-friendly.
- MATCH — You want which row/column a value is in — usually feeding INDEX, not slicing text.
Compatibility (from executed tests)
All three execute identically in every version of Excel, Google Sheets, and LibreOffice we test — this is one area with genuinely zero cross-app surprises. The gotchas are inside the functions themselves (case, wildcards, error types), not between apps.
Example formulas
| Contains check (case-insensitive) | =ISNUMBER(SEARCH("red",A2)) |
| Case-sensitive position | =FIND("ID-",A2) |
| Which row holds the value | =MATCH("cherry",A2:A10,0) |
Full per-version details on each function page: FIND · SEARCH · MATCH.