The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero. Any positive number means the value was found. This means you can use the greater than operator [>] to run a simple test on the result of COUNTIF to force a TRUE or FALSE result:
You can also wrap the formula inside an IF statement to force a specific result. For example, to return "Yes" or "No", use:
This works because IF considers any number > 0 to as TRUE when it appears as the logical test argument.
Testing for a substring or partial match
If you want to test a range to see if it contains specific text [a substring or partial text], you can add wildcards to the formula. For example, if you have a value to look for in cell C1, and you want to check the range A1:A100, you can configure COUNTIF to look for that value anywhere in a cell by concatenating asterisks:
The asterisk [*] is a wildcard for one or more characters. By concatenating asterisks before and after the value in C1, the formula will count the substring in C1 anywhere it appears in each cell of the range.
An alternative formula using MATCH
As an alternative, you can use a formula that uses the MATCH function instead of COUNTIF:
The MATCH function returns the position of a match [as a number] if found, and #N/A if not found. By wrapping MATCH inside ISNUMBER, the final result will be TRUE when MATCH finds a match and FALSE when MATCH returns #N/A.
While working on data in Excel, sometimes we need to check whether one value exists in a column or range of data. We tell Excel to find a value in a column or data range, and if the value exists in a column then return a specified value, else return otherwise.
There are a number of ways to accomplish this task, and this article will show you those methods using examples.
Check if one value exists in a column
When you need to check if one value exists in a column in Excel, you can do this using the MATCH function or VLOOKUP. Here is a description of both with examples.
Check if a value exists in a column using MATCH
Excel’s MATCH function searches for a value in a column or array and returns its relative position based on your chosen match type, whether exact or partial match. If the value is not found, then it returns a #NA error. Its syntax is:
MATCH [value, array, [match_type]]
Suppose we have various invoice numbers in a column and their respective amounts. We want to check if a certain invoice exists in that column, and return “YES,” otherwise return #NA. We check this using MATCH and IF functions in Excel, such as
=IF[MATCH[D3,$A$2:$A$17,0],"Yes"]
This formula uses the MATCH function as a logical condition and If the MATCH function returns relative position of a value, then the IF function returns “YES,” otherwise this formula returns #NA error as shown below.
If we want to see the result in Yes or No instead of Yes or #NA error, then we need to introduce another function IFERROR in above IF statement formula to return “No” to replace #NA error where value not found.
=IFERROR[IF[MATCH[D3,$A$2:$A$17,0],"Yes"],"No"]
The IF function checks whether MATCH function returns a relative position or not? If the MATCH function returns a relative position of a value, then the IF function returns “Yes,” otherwise the IFERROR function replaces #NA error with “No” as shown below.
Check if a value exists in a column using VLOOKUP
The VLOOKUP function searches a value in the first column of a table or data range based on match type and returns its related value from that row in a specified column based on column_index_number. IF the value is found in that column then it returns the value as a result. Otherwise, it returns an #NA error.
Its syntax is;
=VLOOKUP [lookup_value, table_array, column_index_number, [range-lookup]]
Suppose we want to check if a value exists in a column using the VLOOKUP function then return its related value from another column. For example, search an invoice number from the 1st column of a table and return the amount of that invoice. We can do it easily using the VLOOKUP function.
=VLOOKUP[D3,$A$2:$B$17,2,FALSE]
Now suppose we want to check if an invoice exists in a column, otherwise return a blank cell. We can do that by introducing the IFERROR function with the VLOOKUP function in a formula to replace an #NA error, such as;
=IFERROR[VLOOKUP[D3,$A$2:$B$17,2,FALSE],""]
We can see this formula returns blank cell when an invoice is not found in the column.
In this example, now we need to check if a value exists in a column using VLOOKUP function, and if the value is found then return “Yes”, otherwise return “No”, so we can do this by using the VLOOKUP function with the IF and IFERROR functions, like the following:
=IFERROR[IF[VLOOKUP[D3,$A$2:$B$17,2,FALSE],"Yes"],"No"]
In this formula, If the VLOOKUP function searches for an invoice number, then the IF function returns “YES,” otherwise the IFERROR function replaces the #NA error with “No.”
Check if a value exists in a column using Conditional Formatting
The Conditional Formatting feature can be used to check if a value exists in a column or not. If the specified value exists in a column, then conditional formatting highlights that value with an applied formatting style like fill, border, or font, etc.
In this example we want to check if a certain invoice exists in a column using the Conditional Formatting feature and following these steps;
- Select the values in Invoice No. column
- Go to Conditional Formatting > Highlight Cells Rules > Equal to
- A dialog box appears. Insert the value that needs to be searched in column
- Select Formatting Style from the next drop-down list, and press OK
The problem you are trying to solve could be more complex than these examples. If you are in a rush or need a real Expert to help you save hours of struggle, click on this link to enter your problem and get connect to a qualified Excel expert in a few seconds. You can share your file, and an expert will create a solution for you on the spot during a 1:1 live chat session. The first session is free.