In Google Sheets, you can easily check a single value in a range of cells using a function. This time I am detailing this tips below.
Checking the presence of a single value in a range of cells seems easy, right? But I think it’s not so for many. With the search command, it’s OK. But I am talking about using a formula to find any value in a range of cells. Without using the search command we can do it.
It’s common that sometimes you may fall into a dilemma to determine which function to use to get a particular result from the multitude of functions available.
Let us go to our tutorial.
Check a Single Value in a Range of Cells in Google Sheets – How to?
See the image below.
What you will do to find whether the item “Coverall” is available or not on the list. Using IFS function you can find it. But it will be a lengthy procedure. Here is the solution. You can use the following count function in Google Sheets to find it.
=COUNTIF(B5:B15,C6)
Here in this formula B5: B15 is the range to search for a single value and C6 is the value to find. The above formula will return 1, if the value is present, else return 0. Based on this you can further develop the formula.
For example, when you check a single value in a range of cells using the above Countif function, you can perform different calculations based on it.
=if(countif(B5:B15,C6)=1,"This value is existing", "Not in this list")
The above IF logical function can return the result “This value is existing” when the search value is present in the range. This way you can do many calculations like pricing, custom message, any other calculations etc.
This is not the single function that you can use to check a single value in a range of cells. You can also use Match, Regexmatch (for case sensitive match) like functions.
Hope you find this quick google spreadsheet tutorial useful.