In Google Sheets, if you want to check for a value in a range in that case, you need to write the formula. There are two ways to check for the value:
- Checking for the entire Cell Value
- Or a partial value
In this tutorial, we learn to write formulas for both situations.
IF Value Exists in a Range (Entire Cell)
- First, enter the IF in a cell, and then enter the first argument of the IF, enter the COUNTIF.
- Now in the COUNTIF, refer to the range. In the second argument, enter a value that wants to check and close the function.
- After that, enter “>0” within the first argument of the IF to check how many times the value you are looking for is in the range.
- Next, enter the value you want to get if the value is there. And, the value to get is the value you are looking for is not there in the range.
=IF(COUNTIF(A1:A5,"Data")>0,"Yes","No")In this formula, first, we have used COUNTIF, which counts the number of cells where we have the value we are looking for.
And if the count is 1 or more, the value is in the range. And with that, IF uses the condition, and if the count is 1 or more, it returns “Yes”; if zero, then “No”.
IF a partial Value Exists in a Range
Now there might be a situation where you want to check for a partial value in a range. You can use the same formula but with a wildcard character.
=IF(COUNTIF(A1:A5,"*Data*")>0,"Yes","No")
In this formula, as I said, we need to use the asterisk (*) wildcard before and after the value you want to search for.
This formula partially counts the value “Data” from the range, and IF checks whether the count is more than 0. And according to that, returns the value “Yes” or “No”.