I am trying to set up an index match function, which returns the most recent value, instead of the first one in the list. So date should be another constraint for the row number, which I am not able to set up.
In the screenshot , the date constraint is not included, but I tried the following formulas, without any luck:
=INDEX($B$3:$E$11,MATCH(1,($H4=$B$3:$B$11)\*(AGGREGATE(4,4,C3:C11),0),MATCH(I$3,$B$3:$E$3,0))
=INDEX($B$3:$E$11,MATCH(1,($H4=$B$3:$B$11)*(MAXIFS(C3:C11,H4=B3:B11),0),MATCH(I$3,$B$3:$E$3,0))
I would very much appreciate if someone can help me here. Thanks in advance!
Try
=INDEX(D$4:D$11,MATCH(AGGREGATE(14,4,($B$4:$B$11=$H4)*$C$4:$C$11,1),$C$4:$C$11,0))
in I4 and copy across and down
(you may have to enter it as an array formula)