Sometimes we need to do some data validations when we are analyzing a large data set. For example, we need to check whether or not certain values are in the list.
In Excel, we have several methods for finding out if a value exists in a list or not.
In this article, I’ll show you some simple methods you can use to do this.
Method 1: Using Find and Replace
If you don’t want to use Excel functions and formulae, this is the simplest way to see if a value is in a list in Excel.
Below I have a dataset where I have the course subjects listed in Column A. Now I want to see if the subjects listed in Column C are included in the list in Column A.
I can follow the below steps to check if each subject in column C is in the Column A list.
- Select column A.
- Press “Control + F” to open the “Find and Replace” dialog box.
If you don’t like to use the shortcut to open the “Find and Replace” dialog box, you have to go to the “Home” tab, expand the “Find and Replace” dropdown, and select “Find…”.
- Type the subject name that wants to be checked in the “Find what:” box. So, I have typed Finance in that box.
- Click the “Find All” button.
As soon as I click the “Find All” button, I can see the list of cells that contain the searched value. So, in this case, I can see that Cell A6 contains the word Finance. In addition to that, Excel will automatically select the first cell that contains the searched value from the list.
In case you try and find a text string that it can not find, it will show you a message, as shown below. I got this message when I tried to search for Chemistry in column A (which is not present).
In this example, we have used Find and Replace to find all the cells that included that specific string. You can also specify Find and Replace to only find out cells when the entire content of the cell matches the searched string (this can be done by checking the match entire cell contents option).
Also read: Find the Closest Match in Excel (Nearest Value)
Method 2: Using COUNTIF Function to Check If Value is in List in Excel
When you need to check more than one or two values, then the Find and Replace method would not be the best option (as you need to check for each item individually).
In these cases, you can quickly get the results by using a formula to find out whether a value is there is a list or not.
Even though there is no direct Excel function to do this, we can use Excel functions such as the COUNTIF function inside a formula to check if a value is in a list.
Below I have a dataset where I have the course subjects listed in Column A. Now I’d like to see if the subjects listed in Column C are included in the list in Column A.
I want to check whether the subject name is there in the list in column A or not, and if it is, then I want to return a ‘Yes’, else return a ‘No’.
I can use the below formula to do this:
=IF(COUNTIF($A$2:$A$7,C2),"Yes","No")
The syntax of the COUNTIF function is COUNTIF(range, criteria).
For the range argument, I have selected the course subjects list. So, I have selected cells A2 to A7 and pressed F4 to make it an absolute reference (i.e., add a dollar sign before the row and column labels in the reference, such as $A$2).
After I make the cell reference absolute, it does not change when I copy the formula to other cells.
The second argument of the COUNTIF function is criteria. Here I have selected C2 which has the subject name that I want to find in column A.
Next, I have combined this COUNTIF function with an IF function. The syntax of the IF function is IF(logical_test, value_if_true, [value_if_false]). In this case, I have used the result of the COUNTIF function as the logical test of the IF function.
For the second argument, which is true, I want to get “Yes”. So, I have entered the word Yes within quotes.
So, if the count of the given subject is 1 or more than 1, I will get “Yes”.
Then, for the last argument, I have to enter the value I want to show if the logical test is false. I have entered “No” within quotes. So, when the count of the given subject is zero, I will get “No”.
Also read: How to COUNTIF Partial Match in Excel?
Method 3: Using OR Function
You can use the Excel OR function also to check if a value is in a list in Excel.
Below I have a dataset where I have the course subjects listed in Column A.
Now, I want to check whether the subjects given in Column C are included in the list given in Column A.
I can use the below formula to do this:
=OR(($A$2:$A$7)=C2)
The OR function gives TRUE if at least one of the logical test results is true. If all the logical test results are false, it will give FALSE.
The syntax of the OR function is OR(logical1, [logical2], …). I have entered ($A$2:$A$7)=C2 as the logical test of the OR function.
So, if cell C2 matches any cell in the range A2 to A7, the result is TRUE. Otherwise, it will return FALSE.
So, when I check cell C2 (Finance) in the column A list, it matches cell A6 (Finance).
As a result, I get TRUE in cell D2. But, when I checked Chemistry using the formula, Excel couldn’t find any matches. So, I get FALSE in cell D3.
Also read: Using Conditional Formatting with OR Criteria in Excel
Method 4: Using SUMPRODUCT Function
The SUMPRODUCT function in Excel is a hidden gem.
Even though the function name gives a different meaning, you can use the Excel SUMPRODUCT function also to see if a value is in a list.
Below I have a dataset where I have the course subjects listed in Column A. Now, I want to check whether the subjects given in Column C are included in the list given in Column A.
I can use the below formula to do this:
=SUMPRODUCT((($A$2:$A$7)=C2)*1)>0
The syntax of the SUMPRODUCT function is SUMPRODUCT(array1, [array2], [array3], …).
In this case, I applied some conditions inside the SUMPRODUCT function to check if any of the subjects in column A match the subject given in cell C2.
Excel evaluates the condition for each cell in the Column A list and returns either True or False for each cell.
Then I multiplied the results by 1 to convert them to binary values (zeroes and ones). So, I get 1 (one) for True values and 0 (zero) for False values. Then, SUMPRODUCT gets the sum or the total of the ones and zeros.
Then, I used another condition in the formula to see if the sum of the 1s and 0s is more than zero.
If the value is in the list, the sum is greater than zero, and Excel returns TRUE. In this case, the Finance subject is in the Column A list. So, Excel returns TRUE for the formula.
If the value is not found in the list, the sum of the converted values is 0, and the formula returns False. So, for the Chemistry subject, I get False.
Also read: SUMPRODUCT vs SUMIFS Function in Excel
Method 5: Using MATCH and ISNUMBER Functions
We can combine different Excel functions to check if a value is in an Excel list. One such combination is MATCH and ISNUMBER functions.
Below I have a dataset where I have the course subjects listed in Column A. Now, I want to check whether the subjects given in Column C are included in the list given in Column A.
I can use the below formula to do this:
=ISNUMBER(MATCH(C2,$A$2:$A$7,0))
First, I used the MATCH function to find the relative position of a given subject.
The syntax of the MATCH function is MATCH(lookup_value, lookup_array, [match_type]). For the lookup value argument, I have selected cell C2 as I want to search whether Finance is in the list.
For the lookup array, I have selected the course subjects list, which is cells A2 to A7. As I want to do an exact match, I have to enter zero for the last argument.
Then, Excel returns the Finance subject’s relative position. In this scenario, I can see that Finance is the fifth subject on the list. So, the MATCH function returns 5.
If my searched value is not in the list, Excel returns an error. After that, I combined the MATCH function output with the ISNUMBER function.
When the searched item is found in the list, I get a number for the MATCH function result. So, the ISNUMBER function returns true.
If the searched value is not found in the list, the MATCH function does not return a number. So the ISNUMBER function returns FALSE.
Also read: How to Compare Two Cells in Excel? (Exact/Partial Match)
Method 6: Using XLOOKUP, ISERROR, and NOT Functions
Most of the time, when we need to find something in a list, we recall Excel lookup functions.
I can use the newest lookup function, XLOOKUP to check if a value is in a list.
Below I have a dataset where I have the course subjects listed in Column A. Now I want to see if the subjects listed in Column C are included in the list in Column A.
I can use the below formula to do this:
=NOT(ISERROR(XLOOKUP(C2,$A$2:$A$7,$A$2:$A$7)))
The syntax of the XLOOKUP function is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
The XLOOKUP function has six arguments. But, in this case, I have used only the first three arguments (Used only required arguments and ignored optional arguments).
First, I have selected the search value. So, I have selected cell C2 for the first argument.
Then, I selected the list where I wanted to search for the value for the second and third arguments. If the search value is in the list, Excel returns the same search value. If it is not on the list, Excel returns an error.
Then, I used the ISERROR function to check whether I receive an error for the XLOOKUP function.
If there is no error, it means that the searched value is in the list. If there is no error, I want to get True as the answer.
In other words, I want to get the True for the opposite of the ISERROR result. So, I have combined the NOT function with the ISERROR function.
Now, if the XLOOKUP function does not return an error, I get TRUE for my final answer. Otherwise, I get FALSE.
However, the XLOOKUP function is currently available for Microsoft 365 users. Please don’t use this formula in other Excel versions because you will get FALSE for all the results.
Also read: Find Last Occurrence of a Value in a Column in Excel
Method 7: Using VLOOKUP, ISERROR, and NOT Functions
As mentioned earlier, the formula with the XLOOKUP function only works with Microsoft 365 Excel versions.
But the VLOOKUP function works with any Excel version. So it’s important to know how to use the VLOOKUP function to find out whether a value is listed in an Excel list.
Below I have a dataset where I have the course subjects listed in Column A. Now, I want to check whether the given subjects in Column C are included in the list given in Column A.
I can use the below formula to do this:
=NOT(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,FALSE)))
The syntax of the VLOOKUP function is VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).
For the first argument of the function, I have to select the value that I want to search. As I want to search for the value in cell C2, I have selected cell C2 for the first argument.
Then, for the second argument, I have to select the list in which I have to search for the selected value.
In this case, it is cells A2 to A7. So, I have selected cells A2 to A7 and pressed F4 to make it an absolute reference (i.e., add a dollar sign before the row and column labels in the reference, such as $A$2).
After I make the cell reference absolute, it does not change when I copy the formula to other cells.
I have entered 1 for the third argument as I want to return the same value in the list. I have selected False for the last argument as I want to do an exact match.
I get the same search value if the search value is on the list. If it isn’t on the list, I get an error.
My next step is to use the ISERROR function to see if there is an error with the VLOOKUP function.
If there is no error, it means that the searched value is present in the list. So, if there is no error, I want the value to be TRUE.
In other words, I want the TRUE value for the opposite of the ISERROR result. So I combined the NOT function with the ISERROR function.
Now, I get TRUE if the VLOOKUP method does not return an error. Otherwise, the formula returns FALSE.
Also read: Excel If Statement With Multiple Conditions
Method 8: Using FILTER, ISERROR, and NOT Functions
Microsoft has recently added some useful functions. One such handy function is the FILTER function.
To check if a value is included in the list, I can also use the FILTER function.
Below I have a dataset where I have the course subjects listed in Column A. Now, I want to check whether the subjects given in Column C are included in the list given in Column A.
I can use the below formula to do this:
=NOT(ISERROR(FILTER($A$2:$A$7,$A$2:$A$7=C2)))
The syntax of the FILTER function is FILTER(array,include,[if_empty]). I have selected the course subjects list for the first argument of the function. So, I have selected cells A2 to A7.
In the next argument, I have entered a condition. The condition is to filter subjects from the column A list that matches the given subject in cell C2.
If the search value is in the list, I get the filtered results. If it is not on the list, I get an error.
After that, I used the ISERROR function to check whether I got an error for the FILTER function.
If there is no error, it means that the searched value is in the list. So, when there is no error, I want to get TRUE. In other words, I want to get the TRUE for the opposite of the ISERROR result.
So, I have combined the NOT function with the ISERROR function. Now, when the FILTER function does not return an error, I get TRUE. Otherwise, I get FALSE.
We’ve now covered eight methods to check whether a value exists in an Excel list.
You can change these functions and formulas, such as using wildcards, to make them more specific to your needs.
Other Excel articles you may also like:
