asked on

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

Subodh Tiwari (Neeraj)

Why not just try this?

In D1

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

Open in new window

and copy it down.

herbalizer

ASKER

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

Subodh Tiwari (Neeraj)

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.

herbalizer

ASKER

Yes. that'll do it !! Thanks
Not very familiar with excel ...

herbalizer

ASKER

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

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

herbalizer

ASKER

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 !

Subodh Tiwari (Neeraj)

You're welcome! Glad it is working as desired.
Please close the question by accepting the answer.

herbalizer

ASKER

thanks