How to search in Google Sheets: Find, find and replace, and the SEARCH function
Ctrl + F finds text on the current tab, Find and replace searches all tabs or one column, and the SEARCH function locates text inside a cell so that other formulas can use the result. This guide covers all three, with examples and fixes for the errors SEARCH returns.
To search in Google Sheets, press Ctrl + F (Cmd + F on a Mac), type the text and press Enter to jump from one match to the next. For more control, such as searching every tab, one column, or with case matching, open Find and replace with Ctrl + H (Cmd + Shift + H on a Mac). To find text with a formula, use the SEARCH function, which returns the position of a piece of text inside a cell.
| You want to | Use |
|---|---|
| Find a word on the current tab | Ctrl + F |
| Search all tabs, one column or a range | Edit → Find and replace |
| See only the rows that contain a word | A filter, or the FILTER function |
| Highlight every match | Conditional formatting |
| Test or locate text inside a cell with a formula | SEARCH |
Note: Originally published in June 2024. Fully updated in October 2026: the guide now starts with the built-in search tools, covers searching a single column, and corrects the error section (SEARCH returns #VALUE!, not #N/A, when the text is not found).
Search a sheet with Ctrl + F
Press Ctrl + F (Windows, ChromeOS) or Cmd + F (Mac). A small box opens in the top right corner of the sheet. Type what you are looking for: matching cells are highlighted, and Enter or the arrows in the box move through them.
This quick search looks at the current tab only and has no options. For anything else, click the three dots in the box, which open Find and replace.
Search all tabs or a range with find and replace
Search all tabs or a range with find and replace
Find and replace is the full search tool. Despite the name, you do not have to replace anything.
- Go to Edit → Find and replace, or press Ctrl + H (Windows) or Cmd + Shift + H (Mac).
- Type the text in the Find box.
- In the Search drop-down, choose All sheets, This sheet or Specific range.
- Click Find. Keep clicking to move through the matches. When you see “No more results found, looping around”, you are back at the first one.
The checkboxes narrow the search:
- Match case treats “Smith” and “smith” as different.
- Match entire cell contents skips cells where the text is only part of the content.
- Search using regular expressions lets you search by pattern.
- Also search within formulas looks at the formula text, not only at the displayed result.
- Also search within links includes the URLs behind linked cells.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Search within one column
There are three ways, depending on what you want to see.
Jump between the matches. Click the column letter to select the column, then open Find and replace. The Search drop-down is already set to Specific range with that column filled in.
Show only the matching rows. Select the data and choose Data → Create a filter. Click the filter icon in the column header, choose Filter by condition → Text contains, and type the word. Rows without it are hidden until you clear the filter.
Return the matching rows with a formula. FILTER combined with SEARCH lists the rows where column B contains the text, in a separate place and without hiding anything:
=FILTER(B3:C10, ISNUMBER(SEARCH("john", B3:B10)))
The result updates when the data changes, which the two manual methods do not.
Highlight every match with conditional formatting
Highlight every match with conditional formatting
Conditional formatting marks all the cells that contain a word, and keeps marking new ones as they appear.
- Select the cells you want to search.
- Go to Format → Conditional formatting.
- Under Format cells if, choose Text contains and type the word.
- Pick a fill or text color and click Done.
The SEARCH function
SEARCH returns a number: the position at which a piece of text first appears inside another text. If cell B3 contains “John Smith”, =SEARCH("Smith", B3) returns 6, because “Smith” starts at the sixth character.
On its own that number is rarely the goal. SEARCH matters because other functions use it: ISNUMBER turns it into a yes or no, MID and LEFT use it to cut text at the right place.
SEARCH is not case-sensitive: “smith” and “Smith” are the same to it. For a case-sensitive search, use FIND, which has the same arguments.
SEARCH function syntax
=SEARCH(search_for, text_to_search, [starting_at])
- search_for: the text to look for.
- text_to_search: the text, or the cell, to look in.
- starting_at (optional): the character position at which to start looking. The default is 1.
To insert it from the menu instead of typing, go to Insert → Function → Text → SEARCH.
SEARCH function examples
Example 1: find the position of a word
Cell B3 contains “John Smith”. To find where “Smith” starts:
=SEARCH("Smith", B3)
The function returns 6, the position of the first letter of “Smith”.
Example 2: SEARCH ignores case
Cell B5 contains “Alex Johnson”. Searching for the name in lowercase still finds it:
=SEARCH("alex", B5)
The function returns 1, because lowercase and uppercase letters are treated as the same.
Example 3: search by pattern with wildcards
SEARCH accepts wildcards: an asterisk (*) stands for any number of characters and a question mark (?) for exactly one. Cell B7 contains “Maria Garcia”. To find a part of the text that starts with “g” and ends with “a”:
=SEARCH("g*a", B7)
The function returns 7, the position where “Garcia” starts.
Example 4: find the position of a character
To find the first “c” in “Maria Garcia” in cell B7:
=SEARCH("c", B7)
The function returns 10.
Example 5: start the search at a given position
The third argument skips the beginning of the text. To find the second “a” in “Maria Garcia”, start after the first one, at character 3:
=SEARCH("a", B7, 3)
The function returns 5. The position is still counted from the start of the text, not from the starting point.
Combining SEARCH with other Google Sheets functions
Combining SEARCH with other Google Sheets functions
ISNUMBER and SEARCH: does the cell contain the text?
SEARCH returns a number when it finds the text and an error when it does not. ISNUMBER turns that into TRUE or FALSE, which is the usual way to test whether a cell contains a word:
=ISNUMBER(SEARCH("John", B3))
Wrap it in IF for a readable answer: =IF(ISNUMBER(SEARCH("John", B3)), "Found", "Not found"). To test a whole column with one formula, wrap it in ARRAYFORMULA: =ARRAYFORMULA(ISNUMBER(SEARCH("John", B3:B10))).
IFERROR and SEARCH: replace the error
When the text is not found, SEARCH returns #VALUE!. IFERROR replaces the error with a value of your choice:
=IFERROR(SEARCH("Brown", B3), "Not found")
The formula returns the position of “Brown” if cell B3 contains it and “Not found” if it does not. Remember which way round this works: the second argument of IFERROR is what you get when the text is missing.
LEN and SEARCH: count the characters after a point
The LEN function returns the length of a text. Subtract the position that SEARCH returns and you get the number of characters after it. For “Maria Garcia” in cell B7:
=LEN(B7) - SEARCH("G", B7)
LEN returns 12 and SEARCH returns 7, so the result is 5: five characters follow the “G”.
MID and SEARCH: extract the text after a separator
MID cuts text out of a cell from a given position. SEARCH supplies that position. To extract the last name from a full name:
=MID(B3, SEARCH(" ", B3) + 1, LEN(B3))
- SEARCH(” “, B3): finds the position of the first space.
- + 1: moves the start to the character after the space.
- LEN(B3): is longer than the remaining text, so MID returns everything up to the end.
Drag the formula down to extract the last name in every row.
Note: this works when there is exactly one space between first and last name. With middle names, the formula returns everything after the first space.
Finding a value in a table: VLOOKUP and MATCH
Finding a value in a table: VLOOKUP and MATCH
SEARCH looks inside one cell. When the question is “which row has this value, and what is next to it”, you need a lookup function.
VLOOKUP returns the value next to a match
VLOOKUP searches the first column of a range and returns a value from the same row:
=VLOOKUP(search_key, range, index, [is_sorted])
With names in column B and salaries in column C, this returns the salary of Chris Brown:
=VLOOKUP("Chris Brown", B3:C10, 2, FALSE)
The result is 3500. FALSE asks for an exact match; if the name is not in the list, VLOOKUP returns #N/A.
💡 For data arranged in rows instead of columns, see the HLOOKUP function.
MATCH returns the position of a match
The MATCH function returns the position of a value in a range:
=MATCH("Chris Brown", B3:B10, 0)
“Chris Brown” is in cell B8, the sixth cell of the range B3:B10, so the function returns 6. The 0 asks for an exact match.
Errors in the SEARCH function
#VALUE!: the text was not found
⚠️ Error: SEARCH returns #VALUE! when it cannot find search_for in the text. Hovering over the cell shows the reason, for example “In SEARCH evaluation, cannot find ‘666’ within ‘John Smith’”.
✅ Solution: Check the spelling and look for extra spaces in either text. If a missing match is a normal outcome, wrap the formula in IFERROR or ISNUMBER, as shown above.
#VALUE!: the starting position is out of range
⚠️ Error: SEARCH also returns #VALUE! when starting_at is 0, negative, or larger than the length of the text.
✅ Solution: Use a starting position between 1 and the length of the text. If the position comes from another formula, check what that formula returns.
#ERROR!: the formula cannot be read
⚠️ Error: #ERROR! with the message “Formula parse error” means the formula is not written correctly: a missing quote, a missing comma or an unclosed parenthesis.
✅ Solution: Compare the formula with the syntax, =SEARCH(search_for, text_to_search, [starting_at]). Text must be in quotes, cell references must not be.
Related Google Sheets functions
Related Google Sheets functions
Functions that are often used next to SEARCH, each with its own guide:
- XLOOKUP: Searches a range and returns the item that corresponds to the first match.
- QUERY: Filters, sorts and aggregates a range with an SQL-like syntax.
- CONCATENATE: Joins several pieces of text into one string.
- UNIQUE: Returns the distinct values of a range.
- ARRAYFORMULA: Applies a formula to a whole column or range at once.
- Pivot tables: Summarize a large dataset by categories.
- FILTER: Returns the rows of a range that meet your conditions.
- Wrap text: Keeps long text visible inside a cell by breaking it onto several lines.
- REPT: Repeats text a set number of times.
- UPPER/LOWER/PROPER: Changes text to upper case, lower case or title case.
Searching finds what is in the sheet, not what is missing from it
Searching finds what is in the sheet, not what is missing from it
Every method above looks through the rows that are already in the spreadsheet. None of them can tell you that last week is missing from the export, or that the tab was pasted from a report someone filtered first. When a search comes back empty, you cannot tell whether the answer is “no such order” or “no such row in this copy”.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL, and it does not replace Ctrl + F or SEARCH. It makes sure that what you search through is the full, current data set. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How to perform a search in Google Sheets for multiple values?
SEARCH looks for one text at a time. To test a cell for several words, combine the tests with OR, for example =OR(ISNUMBER(SEARCH("john", B3)), ISNUMBER(SEARCH("jane", B3))), or use REGEXMATCH with the words separated by a vertical bar: =REGEXMATCH(B3, "(?i)john|jane").
Where is the SEARCH function located in Google Sheets?
To access the SEARCH function in Google Sheets, simply click on the cell where you want the result to appear, then type "=" followed by "SEARCH" in the formula bar. You can also find the SEARCH function in the "Functions" menu under the "Text" category.
How Google SEARCH function works in single values?
In Google Sheets, the SEARCH function works by searching for a specific substring within a single text value. You simply provide the substring you want to search for, along with the text string you want to search within, and the function will return the position of the substring if it is found.
Google Sheets SEARCH function: How to use it effectively?
To use SEARCH effectively, simply enter the substring you want to search for and the cell containing the text string you want to search within. The function will return the position of the substring if it is found, and an error if it is not present.
Does Google Sheets have a SEARCH function?
Yes. Google Sheets has the SEARCH function, which returns the position of a piece of text inside another text and is not case-sensitive, and the built-in search tools: Ctrl + F for the current tab and Find and replace (Ctrl + H) for all tabs or a range.
