CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
rocker996
rocker996

Reputation: 113

Excel lookup value in all rows, not just 1st match

I have the following table with columns ID and X1, and I want to obtain X2. pic

If one row is YES in the X1 column, then in X2 column all rows with that same ID should be YES. Otherwise it should be NO. For example, ID=106629-1 (yellow) has 2 rows with NO but 1 with YES, then all three should be YES in X2 column. The other 2 IDs (orange and blue) have all rows with NO, then X2 should be NO as well.

I've tried VLOOKUP, but it only considers the 1st match.

Upvotes: 1

Views: 280

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use COUNTIFS to see if any are marked YES

=IF(COUNTIFS(A:A,A2,B:B,"YES"),"YES","NO")

enter image description here

Upvotes: 1

Related Questions