Excel: Find the first instance of a value in column C while searching on column B

I have a data set with Identifiers in column A, a second column containing identifiers in column B and a value in column C.

I want to find all records in column B that has the same identifier as column A, and THEN check the value in column C for each record until I find a value of x.  Therefore, I want the formula to stop looking when it finds the value I'm looking for and return TRUE (or false) in column D.

In this example, I want to find the value = 0 in column C.  Hence, the formula for row 1 should return TRUE since I found a value of 0 on row 3 where A was found in column B.

    A      B      C     D
   
1  N4   N5    1     TRUE
2  N8   N4    1     FALSE (Because it can't find A in column B)
3  N6   N4    0     FALSE (Because it can't find A in column B)
4  N7   N4    1     FALSE (Because it can't find A in column B)

Any thoughts ?
Thanks
herbalizerAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Just limit the ranges used in the formula to max possible rows like this...

=IF(COUNTIFS($B$1:$B$10000,A1,$C$1:$C$10000,"0"),TRUE,FALSE)

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why not just try this?

In D1
=IF(COUNTIF(B:B,A1),TRUE,FALSE)

Open in new window

and copy it down.
0
 
herbalizerAuthor Commented:
Thanks.  However, this only tells me if I can find A in column B.  What I want to do is then check a different column (column C) and verify the value until I get the one I'm looking for.  I've refined the example below

    A      B      C     D
   
1  N4   N5    1     TRUE
2  N8   N4    1     FALSE (Because although it did find N8 in column B (row 5), C=1 in row 5 (and I want to find a 0)
3  N6   N4    0     FALSE (Because it can't find A in column B)
4  N7   N4    1     FALSE (Because it can't find A in column B)
5  N9   N8    1     FALSE (Because it can't find A in column B)

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Then give this a try...

In D1
=IF(COUNTIFS(B:B,A1,C:C,"0"),TRUE,FALSE)

Open in new window

and copy it down.
0
 
herbalizerAuthor Commented:
Yes.  that'll do it !!  Thanks
Not very familiar with excel ...
0
 
herbalizerAuthor Commented:
Hi,
I hate to throw a wrench in there but while it works, it takes too much processing time to apply this formula to a list of 10000 units, which is the size of the file.

Does anybody have a solution that is more efficient in terms of systems resource being used ?

Thanks
0
 
herbalizerAuthor Commented:
Yes, I had done that in the meantime, but the file was also bigger than it needed to be so I cleaned it up and it works fine now.
Thanks a lot !
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it is working as desired.
Please close the question by accepting the answer.
0
 
herbalizerAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.