Assistance with excel formula to match and return value-CodePudding

I'm trying to figure out a formula to return whether or not a ClassID is eligible for a program or not.

Matching the ClassID on sheet 2 with sheet 1, and returning the head of the column from sheet 1 if there is a yes anywhere in the row of that ClassID. Or a No if there is not.

The caveat is there are multiple instances of the ClassID in sequential rows on sheet 2.

CodePudding user response：

You can try:

=IFERROR(INDEX($A$1:$G$1,1,AGGREGATE(15,6,COLUMN(A:G)/(INDEX(A:G,MATCH(J2,A:A,0),0)="Yes"),1)),"No")