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

**LaurenR** · 09-17-2009, 03:26 PM

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

**zbor** · 09-17-2009, 03:34 PM

Like this:

forum%20example(1).xls

**NBVC** · 09-17-2009, 03:36 PM

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

**LaurenR** · 09-17-2009, 03:40 PM

Originally Posted by zbor

Like this:

Attachment 53339

Thanks for the quick reply! It looks great! I just want to make sure that the "Is Client a User" column corresponds to the name that is in columns A and B.

In other words: in row 2, does the "yes" in column E mean that Lillian Gonzalez shows up somewhere in columns C and D? Then the 5 is how many times she is in columns C/D?

Thanks again!

Lauren

**zbor** · 09-17-2009, 03:42 PM

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

**LaurenR** · 09-17-2009, 03:46 PM

Excellent!

One last question. When I double-clicked column F, it appears that the cells selected in columns C/D move down with each row. Should I put $s in to make them stay the same for all rows?

Thanks to both of you for all of your help!

Lauren

**zbor** · 09-17-2009, 03:50 PM

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)

**LaurenR** · 09-17-2009, 03:51 PM

Thanks so much! Have a great day!

**zbor** · 09-17-2009, 03:52 PM

or night

please, make thread [solved]

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions