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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.