Have you ever needed to search for a specific value within a range in Google Sheets? While you may be familiar with using the Ctrl+F or Command+F shortcuts to search for text, these methods search the entire sheet. If you want to search for a value within a specific range, there’s a better way to do it. In this tutorial, we’ll introduce you to some functions that will allow Google Sheets to check if a value exists within a range. This is the fastest way to check if a value exists in a range on Google Sheets.
Watch this short video to see it in action:
Let’s dive in and learn more about this method and a shortcut way to check if a value exists within a range of cells on Google Sheets.
Table of Contents
- Check If a Value Exists in a Range Using the COUNTIF Formula
- Search for Text Within a Range Using the MATCH Formula
- Conclusion
COUNTIF() – Checking the Value in a Range with Google Sheets
The COUNTIF formula is one of the best methods to check if a value exists within a range. The value can be text or a numeric value.
The COUNTIF function is a logical function that checks how many times the condition is TRUE. It’s 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 for all cells, it will return 0. Otherwise, it will return 1 or a higher value.
Syntax:
=COUNTIF(range, criteria)Input Parameters:
- range: The range of cells in which you want to search for the value.
- criteria: The text or numeric value you want to find.
Here’s an example to help you understand it better:
In the example below, we have a list of orders in column A and the items ordered in column B. We want to know how many times the banana has been sold.
The range B2:B10 is the range in which we want to perform the search. D3 is the cell that contains the text “Banana” that we want to search for.
This is how you can search for text within a range.
MATCH() – Searching for Text Within a Range on Google Sheets
Now that you’ve learned how to find values within a cell using the COUNTIF formula, let’s explore another way to do it.
If you don’t want to count the occurrences, but simply confirm if the value exists within a range, you should use the MATCH formula.
Let’s see it in action:
In the previous example, if you use the MATCH formula to find the text “Banana” within the range,
The formula is:
=IF(MATCH(D3,B2:B10,0)>0,"Value exists","Does not exist")This will give the following result:
D3 is the cell that contains the text “Banana”. B2:B10 is the range in which to perform the search. And 0 indicates to search for an exact match.
I combined the MATCH with the IF function to get a boolean output as in the cases TRUE and FALSE. You can also combine it with the ISERROR function.
=IF(ISERROR(MATCH(D3,B2:B10,0)),"Value exists","Does not exist")Here’s the syntax of the MATCH function to help you understand it better:
Syntax:
MATCH(search_key, range, [search_type])Input Parameters:
- search_key: The value to search for.
- range: The range to search within.
- search_type (optional): Optional parameter to specify the search method.
- The default value is 1, which assumes the range is sorted in ascending order and therefore returns the highest value.
- -1 assumes the range is sorted in descending order and therefore returns the lowest value.
- 0 indicates that the range is not sorted, and an exact match should be searched for.
The output of the MATCH function is relative to the position of the search key within the range.
Conclusion
In this tutorial, we learned how to check if a value exists within a range on Google Sheets. We saw how to use the COUNTIF and MATCH formulas to search for text within a range on Google Sheets.
When analyzing data across a large dataset in Google Sheets, you’ll often need these formulas. Go ahead and start using them in your spreadsheets.
Click here to check out more tips and tricks for Google Sheets.
FAQ
Appendix
[1] MATCH Formula to Check if a Value Exists in a Range on Google Sheets – Link
To learn more:
- Getting started with Google Sheets
- Understanding logical functions in Google Sheets
- Managing errors in Google Sheets
