Google Sheets: Search for Value in List and Return Yes or No


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:

Google Sheets if in list then yes

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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *