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

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

Microsoft OfficeMicrosoft ExcelMicrosoft Applications

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

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

Then give this a try...

In D1

In D1

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

and copy it down.
Yes. that'll do it !! Thanks

Not very familiar with excel ...

Not very familiar with excel ...

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

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

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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 !

Thanks a lot !

You're welcome! Glad it is working as desired.

Please close the question by accepting the answer.

Please close the question by accepting the answer.

thanks

In D1

Open in new window

and copy it down.