fbpx
Blog /

How to Search in Google Sheets

In this blog, you’ll learn how to search in Google Sheets, so that you can rapidly identify, reference, and modify data in your spreadsheet.

There are a number of ways to search in Google Sheets. The Find and Replace tool works well for quickly editing data, but a number of Google Sheets functions can also perform this capability.  

Here are the best ways to search in Google Sheets, based on examples and step-by-step walkthroughs.

VIDEO: How to Search in Google Sheets Walkthrough


Search Google Spreadsheet: Find & Replace Tool

Search Real-Time Data in Google Sheets

Before we outline how to search for data with Find & Replace, let’s import a sample dataset from a MS SQL Server database. We’ll connect MS SQL Server to Google Sheets using Coefficient.

With Coefficient, we can pull real-time MS SQL Server data into Sheets automatically. Import database tables into Sheets with a visual data inline previewer, to avoid running SQL queries.  

ms sql server google sheets

Once the dataset is in Sheets, it will automatically sync with MS SQL Server to ensure that the data is always up-to-date.

Microsoft SQL Server Google Sheets

Now that we have our sample dataset, let’s leverage it to learn how to search in Google Sheets.

Find and Replace Tool in Google Sheets

You can easily find text strings in your spreadsheet by using the Find and Replace tool.

Navigate to the top menu and click Edit > Find and Replace.

find and replace tool google sheets

In the Find prompt, enter the text string you want to search for.

find text google sheets

Then click the Find button to locate the cells containing the text string.

The cells will appear in sequential order. When you reach the last cell containing the text string, the next search will reroute to the first cell containing the text string.

search in google sheets

Now let’s see how to replace the specified text string with another text string. First, add a string to the Replace with prompt. Then click the Replace button.

find and replace google sheets

The new string will replace the currently highlighted old string. Press Replace again to replace the next instance of the old string. You can continue doing this until all the old strings are replaced within the Sheet.

find and replace google spreadsheet

Click Replace all if you want to replace every instance of the old string with the new string.

replace all google spreadsheet

Find and Replace Tool: Search Criteria

Search All Sheets in Google Spreadsheet

The find and replace tool in Google Sheets also offers search criteria. For example, you can control the Sheets or cell ranges that are searched.

To search all Sheets within the spreadsheet, select All sheets. Here the search term ‘David’ is found on a different Sheet within the spreadsheet.

all sheets search google spreadsheet

Search Sheet in Google Sheet

The This sheet option only searches the Sheet currently in view.

In this example, there are no instances of ‘David’ on the current Sheet, since the term has already been universally replaced with ‘Dave’.

search sheet in google sheets

Search Specific Range in Google Sheets

You can also search for text strings within specified data ranges. Select Specific range from the drop-down and then click Select data range.

search data range google sheets

In Select a data range, let’s define a search for ‘David” in the Sheet titled ‘Personnel’ across the data range A1:D10, using the following statement:

 Personnel!A1:D10 

search data range google spreadsheet

Now click Replace all to replace all instances of ‘David’ with ‘Dave’ in this data range.

search specific range google sheets

Match Case Search in Google Sheets

If you check the Match case box, the find and replace tool will only locate text strings that match the exact case of your text string.

Over 50K pros building reports use Coefficient to automate business systems data into their Google Sheets
"What an amazing app! Coefficient makes integrating some of the most complex systems unbelievably simple! Set up your integrations in less than a minute."
Yonatan Schvimer

For instance, if we search for ‘Dave’, we find a number of results in the spreadsheet.

match case search google sheets

But if we change the case to ‘dave’, no matches are identified.

match case google spreadsheet

Match Entire Cell Contents in Google Sheets

Match entire cell contents will only find cells that exactly match the text string.

For instance, if we search for a specific address, such as ‘2, rue Royal’, we will only find 1:1 matches.  

match entire cell contents google sheets

However, if we delete the comma, we do not find matches.

match entire cell contents google spreadsheet

Now let’s say the text in the spreadsheet is actually erroneous. If we search for the correct address — 2, Rue Royal — we still do not get a match, because the cases on ‘Rue’ and ‘rue’ do not match.  

match cell contents sheets

With this in mind, you should only use this option when you need to search with granular specificity.

Search Google Sheets with Regular Expressions

In Google Sheets, you can search using regular expressions. This can help you locate a broad range of strings based on certain attributes.

For example, the regular expression ^[a-z].* will highlight all the names that begin with lower case letters.

search with regular expressions google sheets

This allows us to find names that are not properly capitalized, such as ‘dave’, and correct them.

To learn more about how to craft regular expressions, check out the tutorials at RegexBuddy.

Search within Formulas Google Spreadsheet

You can also search within formulas in your Google spreadsheet to find specified strings. Let’s say we created a COUNTIF formula to count the number of times “dave” is listed on the Sheet. The formula would look like this:

=COUNTIF(C1:C13000, “dave”)

If we check the Also search within formulas box, the search will not only locate cells containing “dave”, but also any formulas referencing the text string.

search within formula google sheets

  This locates the COUNTIF formula we’ve inserted at the bottom of the Sheet.

Check the Also search within links box to search links in your spreadsheet for your text string.

search links in google sheets

This allows us to locate the URL for Dave’s Q1 sales pipeline within the spreadsheet.  

search for URL google spreadsheet

Search with Google Sheet Functions

A number of Google Sheets functions can execute searches, including FIND, SEARCH, and MATCH. Here’s an overview of how to use them.

Google Sheets FIND Function

The FIND function in Google Sheets locates the position of a string within a text. The syntax is as follows:

FIND(search_for, text_to_search, [starting_at])

  • search_for is the text string you want to search for
  • text_to_search is the text string in which you will locate the search_for text
  • [starting_at] is an optional argument that identifies what character the search will start from

For instance, suppose you want to find the location of Linda Rousey in this list of high performers by searching for “Rousey”.

The syntax for the FIND formula will look like this:

=FIND (E11, B15)

find function google sheets

E11 is the text string “Rousey” in the Last Name column (E), and B15 is the list of high performers. The result of the formula shows that the last name “Rousey” starts at character 58 in the High Performers list.

Therefore, if we set the FIND formula to start searching the text at character 58, an error is returned:

find in google spreadsheet
find error google sheets

The FIND function is also case sensitive, so a search_for string such as “rousey” would not locate “Rousey” in the list.

Google Sheets Search Function

The SEARCH function is essentially identical to the FIND function in Google Sheets. The syntax is the same:

SEARCH(search_for, text_to_search, [starting_at])

To demonstrate the interchangeability of the functions, watch us simply substitute SEARCH for FIND in the example for the last section.

find formula google sheets
find formula sheets

search function google sheets
search formula google sheets

The result is exactly the same: 57.

Google Sheets MATCH Function

MATCH is a powerful function in Google Sheets. The MATCH function scans A dataset for a specific value and returns its position. The function’s syntax is as follow:  

=MATCH(search_key, range, [search_type])

  • search_key is the record or value you want to find
  • range refers to the column or row you want the function to search
  • search_type is an optional parameter that defines whether the match should be approximate or exact. If omitted, the default is one (1).

1 sorts the range in ascending order, and the function retrieves the largest values that are less than or equal to the search_key.

0 tells the function to look for the exact match in unsorted data ranges.

-1 sorts values by descending sorting order, which tells the function to retrieve the smallest value greater than or equal to the search_key.

As an example, let’s say we want to locate the last name “Ruggerio” in the previous dataset. By applying a MATCH formula to column H, we can identify the row the name appears in:

match function search google sheets

The result reveals that “Ruggerio” is in row 12 of the dataset.

match function search google spreadsheet

Search in Google Sheets: What Method Works Best for You?

There are a number of ways to search in Google Sheets, from the find and replace tool, to built-in functions that allow you to find data by using formulas.

Each method works best in different scenarios, and the right one to deploy will depend on your use case. This guide can help you choose the best option for your team.

If you want more Google Sheets tutorials, check out Coefficient’s other how-to walkthroughs, including:

And to import real-time data into Google Sheets from any business system, try Coefficient for free today.  

Wait, there's more!

Connect any system to Google Sheets in just seconds.

Get Started Free
50,000+ users on
Google Marketplace
Trusted by thousands
of companies