Google sheets is a web-based spreadsheet application and is part of the Google Docs Editors suite.
It is a direct competitor to Microsoft’s Excel, with the major difference being that Google sheets is web-based, and hence allows several users to work online and simultaneously on the same document in real time.
One of the most common tasks with any spreadsheet is searching for information.
When the sheet is small, a visual check may be enough to find the desired information.
However, rarely is a sheet that small, and as with any text based document, a solid search facility is always required.
In this article, we will take a look at a few different ways of searching a Google sheets spreadsheet.
We will be using an example sheet, with the names, addresses, phone numbers, email and web addresses of 500 contacts.
Above, is a screenshot showing the first 50 contacts from the sample sheet.
For the purposes of this article, we’re going to try and find any cells that contain double Zs.
Simple Search
The quickest and easiest way to initiate a search in Google sheets, is to use the keyboard shortcut Ctrl-F.
This will open the Google sheets search panel in the top right of the tools ribbon.
The search field will initially have the placeholder text, Find in sheet.
With the first character you type into the search field, Google sheets starts to look through the sheet for your search term, updating results in real time as you type.
If there is a match anywhere in the sheet, Google sheets will automatically move to that cell and highlight it.
At the same time, on the right hand side inside the search field, will appear a counter showing which match is currently highlighted out of the total found.
In the snapshot above, the characters Zz have been entered in the search field, and we see that the sheet is currently positioned at the first occurrence out of a total of 5.
Highlighted is cell D403, in column D.
As you continue typing characters, the counter will also continue to update.
At any point, as long as matches are available, you can also use the up and down arrows to the right of the search field, to cycle through each of the matching cells.
One thing to note about this search is that it is case insensitive.
The search term above contains a capital Z, yet the matching cell found, D403, contains only lower case Zs.
We will see in a moment how to perform a case sensitive search.
If no more matches are available, then the counter will display 0 of 0, as shown above where the search term Zzz has been entered.
At this stage, you can close the search panel and end your search, edit the existing term and see if that yields any further finds, or enter a totally different search term.
The great advantage of this simple and basic search, is that you always know if there are any matches and exactly how many there are.
Find and Replace
While the previous search method is quick and convenient, there are times when more control over the search is required, such as requiring a case sensitive search.
For this, we need to use the Find and replace functionality of Google sheets.
To activate the Find and replace functionality, you can use the keyboard shortcut Ctrl-H, or from the Google sheets menu bar, choose Find and replace under the Edit menu option.
If you still have the search panel open from before, you can also click on the kebab menu (three vertical dots) on the right of the search panel.
This will also activate the Find and replace functionality.
Whichever method you use, you should now have the Find and replace dialog box displayed in the middle of your screen.
If you were using the simple search method and clicked on the kebab menu in the search panel, then the search term entered in the search entry field will have been copied over to the Find field in the Find and replace dialog box, as seen in the above screenshot.
This is a nice convenience if you decide you have to switch search methods.
Before we proceed to do a search, there are a few things to look at in the Find and replace dialog box. As the name implies, this is not only used to find, but it can also be used to replace cell content.
If you just want to do a plain find, simply leave the Replace with field blank.
Find and replace can also be used across multiple sheets, not just the current sheet.
The dropdown selection in the dialog box, allows you to choose which sheet the search applies to, including All sheets which is the default.
The previous simpler search we looked at, only applied to the current sheet.
Next we come to the four check boxes that give us a few nice options, such as case sensitivity, whether we want to match the entire cell contents, and if the search should also check inside formulas. For those familiar, you can also use regular expressions in your search.
While the extra features just mentioned are handy, one thing the Find and replace method does not have, is the live update of how many matches are found as you type your search term.
In fact, while typing your search term in the Find field, there is no indication if indeed any matches exist or not.
You find that out, only after clicking the Find button.
If we keep the term Zz, select the Match case checkbox and press the Find button, we get the message There are no entries matching Zz.
If we now change our search term to zz and click the Find button again, this time no error is displayed.
We now know that matches have been found, although we don’t know how many.
Clicking the Find button will now cycle through each of the matching cells on the sheet.
Search with Formula
Although this next method is not strictly a find or search in the manner of the two previous methods we’ve seen, in that it does not take you to any matching cells, it can be used to search through a sheet and pull out information through a formula.
Google sheets has a couple of functions called VLOOKUP and HLOOKUP that will search through columns and rows respectively.
There is also a LOOKUP function that searches through both rows and columns, but the rows or columns must be sorted for the search to work correctly.
Since it is more likely that any search will be made down a column than across a row, we will be looking at the VLOOKUP function, although the HLOOKUP function works in exactly the same manner.
The function syntax is as follows:
VLOOKUP(search_key, range, index, [is_sorted])
where search_key is what we are looking for, range is the range of cells to be used in the search, index is the column index from which to return the result, and is_sorted indicates if the column to be searched is sorted or not.
The best way to explain how this function works is to show an example.
From our sample of contacts, we will be searching for a person’s last name, and then printing the company that person is associated with.
We need to use two empty cells anywhere on the sheet, one where we can enter the name to search for, and the second to hold the formula that will display the company name when a match on the name is found.
In the screenshot above, we’ve chosen cells N43 and N44.
The formula we are using is:
=VLOOKUP(N43, B2:C26, 2, FALSE)
Analyzing the formula, N43 refers to the cell which will hold the search key.
We could enter the search term directly into the formula instead of in a cell, but that would not be very practical.
Every time we want to search for a new search term, we would need to modify the formula.
The range for the search is set to B2:C26, meaning that we want our search to only use columns B (last_name) and C (company_name), from rows 2 to 26 inclusive.
We could have included any number of rows and columns, but this will do for our example.
It should be noted that the search itself will only apply to the first column in the range, which in this case is last_name.
We include any other columns in the range if we want to use the corresponding cell data from the match.
The third parameter (2 in our example), refers to the column in the range from which results will be retrieved.
Since the range encompasses two columns (last_name and company_name), an index of 2 means that the result will be pulled from the second column in the range, or company_name.
The last parameter tells Google sheets that our search column is not sorted.
While any search would be more efficient if the column is sorted, it may not always be the case.
Having entered the name Paprocki in cell N43, our formula will search down the last_name column and find a match in cell B5, in the fifth row.
Since the index is set to 2, the formula will pull the result from the corresponding cell in the fifth row of the second column in the range, or C5.
This results in the company name Feltz Printing Service being displayed in cell N44.
Conclusion
We’ve looked at three different ways of performing a search in Google sheets.
A simple search which is more than adequate for most situations, as well as the more comprehensive search and replace.
And to top it off, we examined how to perform a search with a formula.
