CODEWITHSUNDEEP
excelexcel-formula
Tester_Y
Tester_Y

Reputation: 377

How to check if a record exists based on month and persons name (where the name is duplicated)?

I have a data-set with some names and months, sample:

Name    Month
Max       2
Sally     5
Max       1
James     11
Richard   9
Sally     9

I then have a table as such:

        Month
Name      1    2    3    4    5    6    7    8    9    10    11    12
Max
Sally
James
Richard

How do I create a formula that can enter "Yes", or "No" for each month based on the person's name? For example, Max would have months 2 and 1 displaying "Yes", all other months displaying "No".

I can't think of how to do this...using an INDEX/MATCH function I can pull up the first month for "Max", but it won't register the second time his name shows up (it just matches the order of his name with where his data lies in the array, doesn't consider duplicates).

Is there a way to do this? Thanks.

Upvotes: 0

Views: 212

Answers (1)

tigeravatar
tigeravatar

Reputation: 26650

Assuming a data setup like this:

enter image description here

In cell E3 and copied over and down is this formula:

=IF(COUNTIFS($A$2:$A$7,$D3,$B$2:$B$7,E$2),"Yes","No")

Upvotes: 2

Related Questions