Excel - Index/Match to Parse Column Data Until Desired Value Shows-CodePudding

I'm not sure the best way to word this question, but basically I have a big list of different names and IDs that will be visited multiple times with data pulled from a Survey123 form. One of the fields is asking if a part has been repaired, which will be no a maximum of 3 times before turning yes.

I'm using Index/Match to keep track of the dates the visits took place, but if I try it for the repair column it will always just return the first value in the repair column. Is there a way I can have it parse all the repair column values and change the result if it is Yes?

Here is a visual of what I'm trying to achieve, using Index/Match will stop at the first result rather than cycling through.

CodePudding user response：

You may try below formula. If any of visit has Yes in repaired column then it will return Yes or will return No.

=IF(SUMPRODUCT((A3:A5=F3)*(B3:B5=G3)*(D3:D5="Yes"))>0,"Yes","No")

Or you can use XLOOKUP() with Search_Mode option -1 means search last to first order.

=XLOOKUP(1,(A3:A5=F3)*(B3:B5=G3),D3:D5,"",0,-1)

CodePudding user response：

You can use FILTER to achieve this

=FILTER(C2:C4,(A2:A4=F4)*(B2:B4=G4)*(C2:C4="Yes"),"No")