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.
>Solution :
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)
