Tea Rating: ☕

To find a specific text in a specific range we are using the formula;

=COUNTIF(A1:A4;”*“&B2&”*“)>0

This formula we ran for the above situation and the result is TRUE. Actually the main formula is COUNTIF is working here. The first parameter it takes is the range. Here we defined as the cells from A1 to A4 (A1:A4). The second parameter is the keyword to be searched. Here it is coming from the cell B2. The before and after B2 we see “*” which means can be anything. So here we say that we want to check the cells that include B2 and before and after characters are not important.

So in this case if we remove the “*” before the B2 definition, then the result would be FALSE.

=COUNTIF(A1:A4;C2&”*”)>0

This means the value to be searched will be starting with the C2 which is “tree” now and it can end with anything. But as you can see from the range none of the ranges are starting with “tree”. So the result is FALSE.

Another thing we can do with this formula is to count the cells that includes this “tree”. To do that our formula is;

=COUNTIF(A1:A4;”“&C2&”“)

As you can see since we have 2 cells that has “tree” in the A1 to A4 range the result is 2.

Now you can see that you can use the C2 cell as a searchbox and write another thing to search in the range A1:A4 range;

For example if you try “lorem” the result is 1. If you try another thing which is not in the range then it can be zero.

Have a nice tea break.