You know how to search in google sheets using the Ctrl+F or Command+F shortcut. But this will search the entire sheet.
If you want to search for text in a range in google sheets, this method is not the best way to do this. Finding a value in a range is different from finding it in the entire sheet.
In this tutorial, we will learn about a few functions which will let google sheets check if value exists in range.
Here is how to check if value exists in range:
1. Open google sheets on your computer
2. In a new cell write the COUNTIF formula
3. Formula is =COUNTIF(range, criteria to check)
4. Here range is where you want to search
5. Criteria is the text or cell to search
6. Press enter and the COUNTIF function will return the number of occurrences
This is the quickest way to check if value exists in range in google sheets.
Here is a quick video showing this in action:
Let’s learn about this method and another shortcut method in google sheets to check if value exists in a range of cells.
Table of content
- Check if value exists in range using the COUNTIF formula
- Search text in range using the MATCH formula
COUNTIF() – Google sheets check if value exists in range
COUNTIF formula is one of the best methods to find if a value exists in a range. The value can be a text or a numeric value.
COUNTIF function is a logical function that checks how many times the condition is TRUE. It is a combination of the IF function and the Count function.
The output of the COUNTIF function is always a number. If the condition is FALSE in all cells, then it will return 0, or else it will return 1 or higher value.
Syntax
=COUNTIF(range,criteria)
Input parameters
range – The range of cells where you want to find the value
criteria – the text or numeric value that you want to find
Here is an example of this in action:
As you see below, we have a list of orders in column A and items ordered in column B. We want to find out how many times Banana was sold.
Here B2:B10 is the range where we want to search. D3 is the cell that has the text “Banana” that we want to search for.
This is how you will search for text in a range.
MATCH() – Google sheets search for text in a range
Now that you learned how to find values in a cell using the COUNTIF formula, let’s see another way to do this.
If you do not want to find the count but just want to confirm if the value exists in a range, you should use the MATCH() formula.
Let’s see this in a demo:
In the previous example, if you use the MATCH formula to find the text “Banana” in the range,
The formula is:
=IF(MATCH(D3,B2:B10,0)>0,"Value exist","Doesn't exist")
This will give the below output.
D3 is the cell that has the text “Banana”. B2:B10 is the range to search within. And 0 is for specifying to find an exact match.
I have combined MATCH with the IF function to have a boolean output like TRUE and FALSE scenarios. You can combine it with the ISERROR function as well.
=IF(ISERROR(MATCH(D3,B2:B10,0)),"Value exist","Doesn't exist")
Here is the syntax of the MATCH function to understand it better.
Syntax
MATCH(search_key, range, [search_type])
Input parameters
Search_key = the value to search for
range = the range where to search within
Search_type (optional) = optional parameter to define how to search
- The default is 1 which assumes the range is sorted in ascending order and hence returns the largest value
- -1 is to assume the range is sorted in descending order and hence returns the smallest value
- 0 indicates the range is not sorted and hence find an exact match
Output of Match formula
The MATCH function outputs relative to the position of the search key inside the range.
FAQ
How do you check if a range of cells contains a value?
To check if a range of cells contains a value, use the COUNTIF formula. =COUNTIF(range,criteria)
How do you return a value if a given value exists in a certain range in google sheets?
You can use the IF function along with the MATCH function to return a value if the searched value exists in a range. MATCH function will check if value exists in range and the IF function will allow you to return specific values for TRUE or FALSE conditions.
How do I check if a cell is not blank in google sheets?
To check if a cell is not blank in google sheets, use the formula ISBLANK() which will return FALSE if the cell is not blank.
How do I find a substring in a string in google sheets?
You can use the REGEX formula in google sheets to find a substring in a string in google sheets.
In google sheets how to check if value exists in range of another sheet?
To check if value exists in range of another sheet, you can use the COUNTIF formula and pass the Sheet number with the range as the first parameter.
Wrapping up
In this tutorial, we learned how Google sheets check if value exists in range. We saw how to use the COUNTIF and MATCH formulas to search for text in a range in google sheets.
When you are doing data analysis on a large data set in google sheets, you will need these formulas very frequently. Now go ahead and start using them in your worksheets.
Appendix
[1] MATCH formula for google sheets check if value exists in range – Link
Further Reading
New to google sheets ? Start here
More related to Logical functions:
Logical functions in google sheets
Learn more about Google sheets Formulas.
Error handling in google sheets
