We help IT Professionals succeed at work.

Matching names against another list and looking for a specific value assigned to each name.

Note: I am working with Google Sheets but this is almost certainly a universal formula question, so an expert in Excel will likely know how to solve this problem. Thanks.


I am trying to search a list of names (from another sheet) against a static list of names, and when the name is found, indicate if it's next column has a "yes" recorded.

I set up an example working sheet (tab: Agreement Check):

Agreement Check tab:
Column A is a manually entered list.
Column B is temporary and not part of my end requirement but it includes a formula that checks for the existence of the column A name in tab: Cert Data
Column C should indicate Yes or nothing, as pulled from Column B (Agreement Data tab).

Agreement Data tab:
Column A-B is data pulled from a remote sheet.
Column D needs to indicated in Column C (Agreement Check tab)
Column C filters email addresses in Column A to a full name that is used to match against data in the Agreement Check tab.

My description might be overly complicated. Apologies.

The bold Column C (in Agreement Check tab) is where I am having difficulties in creating a working formula. :(

In this column for example...
Betty Lee should have a "yes" in Column C
Bobby Robinson should have nothing in Column C
and so on...

I think I'm 1/2 way to a solution because I know how to determine if the name in column A exists, now I need to check and indicate if the name includes a Yes.

Thank you so much for your help.
Watch Question

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
I believe this is a COUNTIFS problem.
=if(countifs('Agreement Data'!C:C,A4,'Agreement Data'!B:B,"Yes")>0,"Yes","")

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

And FWIW, this is a simpler formula for column B:
=if(COUNTIF('Agreement Data'!C:C,A4)>0,"Found","Not Found")

Open in new window

S ConnellyTechnical Writer


Brilliant! Thank you, byundt.

Thank you so much!
S ConnellyTechnical Writer


Absolutely brilliant solution to my spreadsheet formula question. Thank you.