Compare four columns, give "yes" or "no" answer

Hello! This is my first time posting- I would like to thank everyone for all of the great answers. This time I can't seem to find exactly what I need on another thread, so I thought I would post my own.

I have the attached worksheet with clients' names in four columns (A-D). The first names and last names must remain separate. I need one additional column that shows whether a full name (first and last) in C and D also appears in A and B.

Then one more column that shows how many times the name in C and D shows up, but only if it is also in A and B.

I hope that isn't too confusing. Thanks!

Lauren

Like this:

forum%20example(1).xls

Try:

=ISNUMBER(MATCH(1,INDEX(($C$2:$C$765=A2)*($D$2:$D$765=B2),0),0))

and

=SUMPRODUCT(($C$2:$C$765=A2)*($D$2:$D$765=B2))

respectively and copy them dowm

If there is at least one in C/D then it's yes. If 0, then it's No.

Yes, you can lock range $C$2:$C$1000

also, when you exceed it, don't foget to extend it.
Although you could use C:C but it would slow your workbook slightly.

(same for D)

or night

please, make thread [solved]