As a spreadsheet user, you may often see yourself sifting through data rows to find a specific keyword. On smaller spreadsheets, doing this manually is easy. However, with large spreadsheets, this becomes close to impossible.

Thankfully, Google Sheets has several built-in functions that allow you to easily search for data in your spreadsheets.

4 Ways to Search in Google Sheets

There are multiple ways that you can search text in your spreadsheet. We will discuss some of them extensively here. Google Sheets allows you to use formulas and some built-in functions to search text in your spreadsheets. Here are some methods you can use to search text in your spreadsheet.

The ways to search in Google Sheets we’ll cover in this guide are:

  • The find and replace tool
  • The FIND function
  • The SEARCH function
  • The MATCH function

Each performs slightly differently, so checking them all out will help you find the perfect way to search Google Sheets depending on your specific needs. Whether you're just starting out on spreadsheets or are an experienced entrepreneur, learning to search is a very important skill for Google Sheets.

1. The Find and Replace Tool

This feature is built into Google Sheets and looks through the spreadsheet to find the keyword you provide. It provides a few advantages over using formulas to achieve the same purpose, the most prominent of which is case matching. It is also one of the best basic tools in Google Sheets.

Opening the Built-in Find and Replace Tool

To access the Find and Replace feature in Google Sheets:

  1. Click on Edit in the top bar of your main Google Sheets screen.
  2. Click on Find and replace in the drop-down menu that shows up. This will open a new window in the middle of the screen.

Alternately, you can use the Ctrl + H keyboard shortcut on Windows to do the same thing. On macOS, the shortcut is Cmd + Shift + H.

Perfoming a Search with the Built-in Find and Replace Tool

In the above example, we can find the cells that contain the word "Pencil". In the Find and Replace window, you will see two text boxes: Find and Replace with. In the Find text box, enter your keyword. If you have multiple sheets, choose All sheets in the Search option.

Finally, click on the Find button. This will take you to the first instance of the text in the spreadsheet. You can repeatedly press the button to traverse the spreadsheet to find the keyword.

Alternatively, if you don't plan to replace any data, and you don't need any of the advanced search features, then use the Find feature instead. You can access this by using the Ctrl + F shortcut in Windows. For macOS users, the shortcut is Cmd + F.

2. The FIND Function

You can use this formula to find the position where the string is first detected in the text. This formula is case-sensitive, so ensure the text you enter the formula has the proper case. Here is the syntax for the formula:

        =FIND(search-for, search-text, start-at)
    

The formula uses three arguments. These are:

  • search-for: this is the text you're searching for in the search-text parameter.
  • search-text: this defines the cell or cell range that the function will search to find the text in the search-for parameter.
  • start-at: this is an optional parameter to define the character in the search-text. This defines where the search will be started from.

In this example, we have a sentence in cell A2. We wish to find the word fox there. Here are the steps to do so:

Using the FIND Function
  1. Click on the cell where you wish to input the formula.
  2. Type the initial part of the formula, which is =FIND(.
  3. Now, type the cell address of the text that you want to search. In this case, it is cell D2. Alternately, you can also write the text inside quotation marks.
  4. Write the cell address containing the text you wish to search. In this case, it is cell A2.
  5. Add a closing bracket.
  6. Press Enter to execute the formula.

One thing to note is that this formula is case-sensitive. So if you write “Fox” instead of “fox” then the formula won't work. If you're looking for a similar function that isn't case-sensitive, you can use the SEARCH formula.

Note: If you’re confused by the numerical result in the example, remember the FIND function notes the location of the string, in our case, fox. You’ll notice that the f in fox is the 17 character in the cell (including spaces) hence the result of 17.

3. The SEARCH Function

The SEARCH formula is almost identical to the FIND formula. Here is the syntax for the formula:

        =SEARCH(search-for, search-text, start-at)
    

As you can see, the SEARCH formula uses the same parameters as the FIND formula. We will use a different case for the search text to demonstrate this formula. Here are the steps to use the SEARCH formula.

Using the SEARCH Function
  1. Click on the cell where you wish to input the formula.
  2. Type the initial part of the formula, which is =SEARCH(.
  3. Now, type the cell address of the text that you want to search. In this case, it is cell D2.
  4. Write the cell address containing the text you wish to search. In this case, it is cell A2.
  5. Add a closing bracket.
  6. Press Enter to execute the formula.

4. The MATCH Function

The MATCH function outputs the position of a value in a defined cell range that matches a value specified by the user. Here is the syntax for the formula:

        =MATCH(key, range, type)
    

The formula uses three parameters. These are:

  • key: this is the value that you want to search for. This can be a number, string, or a combination of both.
  • range: this is the 1-dimensional array to search the value within. Note that using a 2-dimensional range will return the #N/A! Error.
  • type: this is an optional parameter that defines the method to search the range.

In the type parameter, you can choose to enter either 1, 0, or -1.

  • 1 is the default and assumes that the range is in ascending order.
  • 0 is used where the range is in no particular order.
  • -1 assumes that the range is in descending order.

Here are the steps you need to follow to use a MATCH formula:

Using the MATCH Function
  1. Click on the cell where you wish to input the formula.
  2. Type the initial part of the formula, which is =MATCH(.
  3. Now, type the text that you want to search for. In this case, we will write “Desk” including the quotation marks.
  4. Add a comma.
  5. Write the cell range containing the text you wish to search. In this case, it is the range B2:B31.
  6. Add another comma to separate the parameters.
  7. As the data is in no particular order, the third parameter is entered as 0.
  8. Add a closing bracket.
  9. Press Enter to execute the formula.

The result is 1 as the first instance of “Desk” is in the first row of the range.

Keeping Searching for Ways to Improve Your Spreadsheet Skills

Aside from SEARCH and FIND, these functions work quite differently and are best used in different scenarios. Familiarize yourself with all of them for the best results in your spreadsheets. Mastering as many Google Sheets functions as possible will have you using the software like a pro in no time.