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

herbalizer
herbalizer used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Why not just try this?

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

Open in new window

and copy it down.

Author

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
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

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
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

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 !
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

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

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial