What Is SEARCH Function In Google Sheets?
The SEARCH Function in Google Sheets returns the position of the selected letter or a string from a text value, i.e. find a position of the substring from within the main string.
The SEARCH Google Sheets Function always returns a numeric value as the output because it will be the position of the letter found. The function is not case-sensitive.
For example, consider the below table with the text string in cell A2. Let us find the position of the substring given in cell B2 using the SEARCH function.
Select cell C2, enter the formula is =SEARCH(B2,A2) and press “Enter”, as shown below.
The output is 5, indicating that the selected letter “s” is in the 5th position in the main string.
Key Takeaways
- The SEARCH Function in Google Sheets is a Text function that helps us search any value as a substring that is found within a selected value. Since we want the position of the substring, the result will always be a number unless it is not found.
- It is not a case sensitive function, So, if we want to find position of a letter from a text it doesn’t matter if the substring is an uppercase or a lowercase letter.
- The start_num must always be 1 or greater than 1, and must not exceed the length of the main string. If this value is not given, then, by default, it is considered as 1 meaning the search is from the starting of the main string.
- If we have multiple occurrences of the substring, then, unless we mention the position to start search, by default, we will always get the position of the first occurrence of the substring. Therefore, we must give the start_num value after the first occurrence of the substring, to get the 2nd or 3rd occurrence, respectively.
- We can use the SEARCH function, along with other functions, such as, IFNUMBER(), VLOOKUP(), IFERROR, etc.
Syntax
The syntax of the SEARCH formula in Google Sheets is,
The arguments of the SEARCH formula in Google Sheets are,
- search_for: It denotes the character or the substring to search in the main value. It is a mandatory argument.
- text_to_search: It is a mandatory argument. It is the text string within which the substring is searched.
- start_num: It shows the position of the text value from which the function begins the search. It is the optional argument. Since it is an optional argument, if the users ignore this argument, the function automatically takes the default value of 1 or the start of the text.
How To Use SEARCH Function In Google Sheets?
We can use the SEARCH Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
#Method 1: Access from the Google Sheets ribbon ->
Step 1: Choose an empty cell for the output -> select the “Insert” tab -> click the “Function” option right arrow -> click the “Text” option right arrow -> select the “SEARCH” function, as shown below.
Step 2: The “SEARCH” formulaappears, as shown below. Enter the argument as cell reference.
#Method 2: Enter the formula in the worksheet manually –
Step 1: Select an empty cell for the output.
Step 2: Type =SEARCH( in the cell, as shown below. [Alternatively, type =S or =SE and double-click the SEARCH from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some SEARCH Function in Google Sheets examples along with other functions, such as VLOOKUP(), ISNUMBER(), IFERROR(), etc.
Example #1
The dataset consists of the famous sitcom actor’s names. We will use the SEARCH function to find the position of their second names.
The steps to find the position of the last names are as follows:
Step 1: Select cell C2 and enter the formula =SEARCH(B2,A2), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C7 using the fill handle, to get the following results.
Example #2 – Combining SEARCH with VLOOKUP Function
Consider the dataset that consists of employee details, such as their names, ID’s, department, date of joining, salary, etc. We will find the position of a substring from the department of a selected employee by combining SEARCH with VLOOKUP Function.
We have created criteria tables in cells H1:I2 and H4:I5 and have executed the VLOOKUP formula in cell I2, for reference. It helps us see the result of part of the formula which becomes one of the argument values for the remaining formula.
The procedure to combine SEARCH and the VLOOKUP formula is,
Select cell I5, enter the formula =SEARCH(“n”,VLOOKUP(H2,B2:E6,4,0)) and press “Enter”, as shown below.
The output is shown above as 3, indicating that the letter “n” is in the 3rd position of the VLOOKUP result“finance”.
Example #3 – Combining SEARCH with ISNUMBER Function
The dataset given below has smartphone names and the substring that must be found by combining SEARCH with ISNUMBER Function.
The steps to combine SEARCH and the ISNUMBER functions are,
Step 1: Select cell C2, enter the formula =ISNUMBER(SEARCH(B2,A2)) and press “Enter”, as shown below.
Step 2: Drag the formula from cell C2 to C9 using the fill handle, to get the following results.
The output shows that combining the SEARCH and the ISNUMBER functions will helps us get TRUE or FALSE results, if the substring is found or not, respectively.
Example #4 – Combining SEARCH with IFERROR Function
The below table consist of a list of company names. Let us find if the selected name is in the list or not by combining SEARCH with IFERROR Function.
The procedure to combine SEARCH and the ISERROR formula is,
Select cell C2, enter the formula =IFERROR(SEARCH(“Amazon”,A2:A7),”Yes”) and press “Enter”, as shown below.
The output shows that combining the SEARCH and the ISERROR functions will helps us get an alternate result, such as Yes or No or any other entered value, instead of the default, TRUE or FALSE results, if the substring is found or not, respectively.
Important Things To Note
- The SEARCH function in Google Sheets is not case-sensitive and always returns a positive integer value.
- If the substring is not found, or if the last argument value, where to start the search from, crosses the substring position, then we will get the #VALUE! error.
- The last argument, if not entered, by default, will be considered as 1. However, when we enter the value it, must always be 1 or greater value than 1.
Frequently Asked Questions (FAQs)
A few reasons the SEARCH Google Sheets Function may not work are,
a. The argument values or cell reference are provided as cell values and are not entered within double-quotes.
b. One or both the mandatory argument values are not provided.
c. The optional argument provided is a negative number, or a value that has exceeded the length of the main string. So, we cannot start from that position.
d. We want the second occurrence of the substring, but have not given the right optional argument value.
We often forget in which category a function falls, here, the “SEARCH” function. Then, we can insert the function as follows:
Choose an empty cell -> select the “Insert” tab -> click the “Function” option right arrow -> click the “All” option right arrow -> select the “SEARCH” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the SEARCH Google Sheets Function by following the path shown below.
• Choose an empty cell à click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option à click the “All” option right arrow à select the “SEARCH” function, as shown below.
We can insert the Google Sheets IFERROR function as follows:
Choose an empty cell for the output -> select the “Insert” tab ->click the “Function” option right arrow -> click the “Logical” option right arrow -> select the “IFERROR” function, as shown below.
We can insert the Google Sheets ISNUMBER function as follows:
Choose an empty cell for the output -> select the “Insert” tab -> click the “Function” option right arrow -> click the “Info” option right arrow -> select the “ISNUMBER” function, as shown below.
We can insert the Google Sheets VLOOKUP function as follows:
Choose an empty cell for the output -> select the “Insert” tab -> click the “Function” option right arrow -> click the “Lookup” option right arrow -> select the “VLOOKUP” function, as shown below.
Download Template
This article must be helpful to understand the Search Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Search Function In Google Sheets. We learn its syntax & how to use it to search for text with examples & working template. You can learn more from the following articles –
Leave a Reply