Some Easy Ways to Search Sheets (With Examples)

As a spreadsheet user, you may often find yourself browsing rows of data back and forth to find a particular keyword. On smaller spreadsheets, doing this manually is easy. However, with large spreadsheets, this becomes almost impossible or stressful.

Fortunately, Google Sheets has several built-in functions that allow you to easily search for data in your spreadsheets. Check how to track changes in Google Sheets with email notifications and edit history.

Some ways to search Google Sheets

There are several ways you can search for text in a spreadsheet. We will discuss some of them in some detail here. Google Sheets allows you to use formulas and some built-in functions to search for a keyword in a simple and seamless manner. Here are some methods that you can use to find text in your spreadsheet.

The Google Sheets search methods we’ll cover in this article are:

• Find and replace tool.
• Find function.
• Search function.
• MATCH function.

They each work a little differently, so checking them all will help you find the perfect way to search Google Sheets according to your specific needs. Whether you are just getting started with spreadsheets or you are a seasoned entrepreneur, learning to research is a very important skill to master with Google Sheets.

1. Find and replace option

This option is built into Google Sheets and searches the spreadsheet to find and replace the keyword you provide. It offers some advantages over using functions for the same purpose, most notably case matching. It is also one of the best and essential tools in Google Sheets.

To access the Find and Replace option in Google Sheets:

• Click Modify in the top bar of the main Google Sheets screen.
• Click Find and Replace in the drop-down menu that appears. This will open a new window in the middle of the screen.

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

Type the word you want to search for next to “Search.” If you want to replace the word, enter the new word next to “Replace with”.

In the above example, we can find the cells containing the word “Pencil”. in the Find and Replace window. In the Search text box, enter your keyword. If you have multiple papers, select All papers in the search option.

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

Alternatively, if you do not plan to overwrite any data, and do not need any of the advanced search features, use the search option instead. You can access it with a shortcut Ctrl + F in Windows. For macOS users, the shortcut is Cmd + F.

Optional: You can narrow your search using one of the options below:

• case matching: Matches words with the same capital letters.
• Matching using regular expressions: Matches words based on regular expressions in a Google Spreadsheet.

2. Find . function

You can use this function to find the position where the string was first discovered in the text. This syntax is case sensitive, so make sure the text you enter has the proper case. Here is the function syntax:

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

The function uses three arguments. Which is:

• search-for: This is the text you are searching for in the search text parameter.
• search-text: Specifies the cell or range of cells in which the function will search to find the text in the search parameter.
• start from (search-at): This is an optional parameter to identify the character in the search text. This determines where the search will begin.

In this example, we have a sentence in cell A2. We’ll try to find the word Fox there. Here are the steps to do that:

• Click the cell where you want to enter the formula.
• Write the first part of the formula, which is =FIND(.
• Now, type the cell address of the word you want to search for. In this case, the cell is D2. Alternately, you can also type the word inside the quotes.
• Type the address of the cell that contains the text you want to search. In this case, the cell is A2.
• Add a closing bracket.
• Click on Enter to implement the formula.

One thing to note is that this syntax is case sensitive. So if you type “Fox” instead of “fox” the function will not work. If you are looking for a similar function that is not case sensitive, you can use the Search.

note: If you are confused about the numerical result in the example, remember that the FIND function indicates where the word is, in our case, fox. You’ll notice that the f in fox is the 17th in the cell (including spaces) so the result is 17.

3. Search . function

The Search function is almost identical to the FIND function. Here is the syntax of the formula:

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

As you can see, the SEARCH function uses the same parameters as the FIND function. We’ll use a different case for the search text to illustrate this syntax. Here are the steps for using the Search function.

• Click the cell where you want to enter the formula.
• Write the first part of the function, which is =SEARCH(.
• Now, type the cell address of the word you want to search. In this case, the cell is D2.
• Type the address of the cell that contains the text you want to search. In this case, the cell is A2.
• Add a closing bracket.
• Click on Enter to implement the formula.

Search is not case sensitive.

4. MATCH . function

The MATCH function outputs the position of a value in a specified range of cells that matches the user-specified value. Here is the syntax of the formula:

=MATCH(key, range, type)

The function uses three parameters. Which is:

• key: This is the value you want to find. This can be a number, a string, or a combination of both.
• range: The one-dimensional array to find the value in. Note that using a 2D range will return the error #N/A! error.
• type: This is an optional parameter that defines how to search the scope.

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