Check IF a Value Exists in a Range (Formula in Excel)

In Excel, to check if a value exists in a range or not, you can use the COUNTIF function, with the IF function. With COUNTIF you can check for the value and with IF, you can return a result value to show to the user. i.e., Yes or No, Found or Not Found.

Check for a Value in a Range

In the following example, you have a list of names where you only have first names, and now, you need to check if “GLEN” is there or not.

You can use the following steps:

1. First, you need to enter the IF function in cell B1.
   
2. After that, in the first argument (logical test), you need to enter the COUNTIF function there.
   
3. Now, in the COUNTIF function, refer to the range A1:A10.
   
4. Next, in the criteria argument, enter “Glen” and close the parentheses for the COUNTIF Function.
   
5. Additionally, use a greater than sign and enter a zero.
   
6. From here, enter a comma to go to the next argument in the IF, and enter “Yes” in the second argument.
   
7. In the end, enter a comma and enter “No” in the third argument and type the closing parentheses.
   

The moment you hit enter it returns “Yes”, as you have the value in the range that you have searched for.

=IF(COUNTIF(A1:A10,"Glen")>0,"Yes","No")

Want to learn more? Check out this Free Courses: 50 Excel Formulas to Get Smarter than Your Colleagues

How this Formula Works

This formula has two parts.

In the first part, we have COUNTIF, which counts the occurrence of the value in the range. And in the second part, you have the IF function that takes the values from the COUNTIF function.

So, if COUNTIF returns any value greater than which means the value is there in the range IF returns Yes. And if COUNTIF returns 0, which means the value is not there in the range and it returns No.

Check for a Value in a Range Partially

There counts to be a situation where you want to check for partial value from a range. In that case, you need to use wildcard characters (asterisk *).

In the following example, we have the same list of names but here is the full name. But we still need to look for the name “Glen”.

The value you want to search for needs to be enclosed with an asterisk, which we have used in the above example. This tells Excel, to check for the value “Glen” regardless of what is there before and after the value.

=IF(COUNTIF(C1:C10,"*"&"Glen"&"*")>0,"Yes","No")