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.  

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

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.

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

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.

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.

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.

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

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.

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 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.

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 

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

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.

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

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

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.  

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

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.  

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.

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.

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

Search within Links in Google Sheets

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

This allows us to locate the URL for Dave’s Q1 sales pipeline within the 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)

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:

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.

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:

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

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:

• The Ultimate Guide to Using INDEX in Google Sheets
• How to Use the Google Sheets QUERY Function
• The Ultimate Guide to VLOOKUP in Google Sheets

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