You can use the following basic syntax to check if a value in a cell exists in a list in Excel and return either “Yes” or “No” as a result:
=IF(COUNTIF($A$2:$A$14, D2)>0,"Yes","No")
This particular formula checks if the value in cell D2 exists in the range A2:A14.
If the value does exist in the range, then the formula returns “Yes” as a result.
Otherwise, the formula returns “No” as a result.
The following example shows how to use this formula in practice.
Example: Search for Value in List and Return Yes or No in Excel
Suppose we have the following two lists of basketball players in Excel:
Suppose we would like to determine whether or not each player in List B exists in List A.
To do so, we can type the following formula into cell E2:
=IF(COUNTIF($A$2:$A$14, D2)>0,"Yes","No")
We can then click and drag this formula down to each remaining cell in column E:
The formula returns either “Yes” or “No” for each player in List B.
For example, we can see:
- Harry does exist in List A.
- Mark does not exist in List A.
- Chad does exist in List A.
- John does exist in List A.
And so on.
Note: If you’d like to return values other than “Yes” and “No”, simply replace these values in the formula with whatever values you’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Write a Case Statement
Excel: How to Find Unique Values from Multiple Columns
Excel: How to Match Two Columns and Return a Third