You can use the following formula to check if a value in a cell exists in a list in Google Sheets 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 Google Sheets
Suppose we have the following two lists of basketball players in Google Sheets:
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 Google Sheets:
Google Sheets: How to Check if Multiple Cells are Equal
Google Sheets: How to Check if Multiple Cells are Equal
Google Sheets: Change Color when Checkbox is Checked
