As a spreadsheet user, you can often find yourself sifting through rows of data to find a specific keyword. On smaller spreadsheets, it’s easy to do this manually. However, with large spreadsheets, this becomes almost impossible.
Fortunately, Google Sheets has several built-in functions that allow you to easily find data in your spreadsheets.
4 Ways to Search Google Sheets
There are several ways to search for text in your spreadsheet. We will discuss it at length here. Google Sheets lets you use formulas and some built-in functions to find text in your spreadsheets. Here are some methods you can use to find text in your spreadsheet.
The search methods in Google Sheets that we will cover in this guide are:
- The search and replace tool
- The FIND function
- The SEARCH function
- The MATCH function
Each works slightly differently, so checking them all out will help you find the perfect way to search Google Sheets for your specific needs. Whether you’re new to spreadsheets or you’re an experienced entrepreneur, learning how to search is a very important skill for Google Sheets.
1. The Find and Replace Tool
This feature is built into Google Sheets and it scans the spreadsheet to find the keyword you provide. It offers a few advantages over using formulas to achieve the same goal, the most important of which is case matching. It is also one of the best basic tools in Google Sheets.
To access the Find and Replace feature in Google Sheets:
- Click on Edit in the top bar of your main Google Sheets screen.
- Click on 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 CTRL+H keyboard shortcut in Windows to do the same thing. On macOS, the shortcut is Cmd + Shift + H.
In the example above, we can find the cells that contain the word “Pencil”. In the Find and Replace window, you will see two text boxes: To find and Replace with. In the To find text box, enter your keyword. If you have multiple sheets, choose All sheets in the Look for option.
Finally, click on the To find button. This will take you to the first instance of the text in the spreadsheet. You can press the button repeatedly to browse the worksheet to find the keyword.
Alternatively, if you don’t plan to overwrite data and don’t need any of the advanced search features, use the To find feature instead. You can access it using the CTRL+F shortcut in Windows. For macOS users, the shortcut is Command + 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 make sure the text you enter in the formula has the correct case. Here is the syntax of the formula:
=FIND(search-for, search-text, start-at)
The formula uses three arguments. These are:
- to research: this is the text you are looking for in the search text setting.
- search text: this defines the cell or range of cells that the function will search to find the text in the to research setting.
- start at: it is an optional parameter to define the character in the search text. This defines where the search will be launched from.
In this example we have a sentence in the cell A2. We want to find the word fox there. Here are the steps to follow:
- Click the cell where you want to enter the formula.
- Type the initial part of the formula, which is =FIND(.
- Now type the cell address of the text you want to search. In this case, it is the cell D2. Alternatively, you can also write the text in quotes.
- Write the address of the cell containing the text you want to find. In this case, it is the cell A2.
- Add a closing parenthesis.
- Hurry Walk in to run the formula.
One thing to note is that this formula is case sensitive. So if you write “Fox” instead of “fox”, the formula will not work. If you are looking for a similar function that is case insensitive, you can use the SEARCH formula.
To note: If you are confused by the numerical result of the example, remember that the FIND function notes the location of the string, in our case, fox. You will notice that the f in fox is the 17th 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 of 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.
- Click the cell where you want to enter the formula.
- Type the initial part of the formula, which is =SEARCH(.
- Now type the cell address of the text you want to search. In this case, it is the cell D2.
- Write the address of the cell containing the text you want to find. In this case, it is the cell A2.
- Add a closing parenthesis.
- Hurry Walk in to run the formula.
4. The MATCH function
The MATCH function outputs the position of a value in a defined range of cells that matches a user-specified value. Here is the syntax of the formula:
=MATCH(key, range, type)
The formula uses three parameters. These are:
- key: this is the value you want to search for. It can be a number, a string, or a combination of both.
- interval: this is the one-dimensional array in which to look up the value. Note that using a 2-dimensional range will return the #N/A! Mistake.
- type: this is an optional parameter that defines the search method in the range.
In the type parameter, you can choose to enter either 1, 0Where -1.
- 1 is the default and assumes the range is in ascending order.
- 0 is used when the range is not in any particular order.
- -1 assumes the range is in descending order.
Here are the steps to follow to use a MATCH formula:
- Click the cell where you want to enter the formula.
- Type the initial part of the formula, which is =MATCH(.
- Now type the text you want to search for. In this case we will write “Desk” including quotation marks.
- Add a comma.
- Write the range of cells containing the text you want to find. In this case, it is the range B2:B31.
- Add another comma to separate the parameters.
- Since the data is not in any particular order, the third parameter is entered as 0.
- Add a closing parenthesis.
- Hurry Walk in to run the formula.
The result is 1 because the first instance of “Desk” is in the first row of the range.
Keep looking 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 each one to get the best results in your spreadsheets. Mastering as many Google Sheets features as possible will have you using the software like a pro in no time.