More help with a funtion

[Jean]

I'm getting closer to an answer, but I still don't have
it because I'm asking this so poorly. Take ALL the
numbers in Column B and find a match in Column A. I need
a formula in Column D that would determine if any number
in B matches any number in A. If there is a match, there
has to be a 1 in Column C for the match in Column A, not
Column B.

A B C D
765 765 1
768 768 2
770 768 1
768 770 2
765 771 2

I was given the following helpful information, but it
won't work for the very first one. The first one would
be FALSE because of this.

Hi:

=AND(NOT(ISNA(MATCH(B1,A:A,0))),C1=1)

Copy down.

Regards,

Vasant.

 

[Dave R.]

So you want TRUE if a match is found and there is a 1 in C? For future
reference it it sometimes helpful to describe what you are doing so that the
problem makes more sense to ppl trying to help.

In the meantime, this may be what you want:

=+AND(COUNTIF(A$1:A$50,B1)>0,C1=1)

 

[Vasant Nanavati]

Hi:

1. Don't start new threads for following up on the same question.

2. Switch the column A and column B references in the solution I provided
earlier.

Regards,

Vasant.

 

[Dave R.]

Also, you may want to list the desired outcomes in column D.

If there is a match, there has to be a 1 in Column C for the match in

Column A, not Column B.

What does that sentence mean?

 

[Jean]

Thanks, Vasant. I'm new to these Newsgroups...they are
terrific, but I'm learning the protocol.

I had tried switching the references prior to your email,
but it still didn't work. I'll continue working on it.
Perhaps it's because the information is on different
sheets, which I didn't really think would make a
difference. I think I just need to tweak things a
bit...I'm almost there, thanks to your formula.

You've been a big help and I thank you again!!

Jean

 

[Jean]

Thanks for your help, Dave. I'm still perfecting my
skills with these Newsgroups, which are just fantastic
(the Newsgroups, that is)!!

 

[Vasant Nanavati]

Jean, the following works perfectly for me:

=AND(NOT(ISNA(MATCH(A1,B:B,0))),C1=1)

Am I missing something?

Regards,

Vasant.

 

[Jean]

It works just great, Vasant. I just needed to adjust the
cell references because my actual data was on different
sheets. I can't thank you enough for your help and
time!!!