SEARCH vs FIND: When Case Sensitivity Matters

Excel & Google Sheets

How it works

Both functions return the character position where text is first found — in "Desk Lamp - Black", the word starts at position 6 — and both return #VALUE! when it isn't found at all. The difference is what counts as found. SEARCH is case-insensitive: "lamp" matches "Lamp", "LAMP", and "lamp" alike, which is what messy real-world data needs. FIND is case-sensitive: =FIND("lamp",A2) on "Desk Lamp" returns #VALUE! because the capital L doesn't match — the single most common surprise with these functions. SEARCH also accepts wildcards (? for one character, * for any run), so =SEARCH("l?mp",A2) matches lamp and limp; FIND treats ? and * as literal characters. Default to SEARCH, and reach for FIND only when capitalization is the point — like telling product code "AB" from "ab".

"lamp"

The text to locate. SEARCH matches any capitalization; FIND requires an exact-case match.

A2

The cell to look inside. The result is the position (a number), or #VALUE! if absent.

When to use it

Use SEARCH to locate a word before splitting with LEFT/MID, or to flag rows containing a keyword regardless of typing. Use FIND when case is meaningful — distinguishing "IT" the department from "it" the word.

Common mistakes

• ✕Using FIND on mixed-case data.

  ✓=FIND("lamp",A2) fails with #VALUE! on "Desk Lamp" — the capital L is a miss. Switch to =SEARCH("lamp",A2), which returns 6 on the same cell.

• ✕Using the raw result as a yes/no flag.

  ✓SEARCH returns a position or #VALUE!, never TRUE/FALSE. Wrap it: =ISNUMBER(SEARCH("lamp",A2)) gives a clean TRUE or FALSE for filtering and IF tests.

• ✕Searching for a literal ? or * with SEARCH.

  ✓SEARCH treats ? and * as wildcards, so =SEARCH("*",A2) matches everything. Escape with a tilde — =SEARCH("~*",A2) — or use FIND, which takes them literally.