Hi here is my made up data:
I am trying to get the last occurrence of a data with the two conditions (see result). I have tried to use index, match and max, but it always return the first one. Many thanks for the help. Here is my code so far:
=INDEX(col3,MATCH(1,(E3=colour)*(F3=code),0))
Appreciate the help
In Excel O365 you could utilize XLOOKUP with some boolean logic:
Formula in G1:
=XLOOKUP(1,(A$1:A$9=E1)*(B$1:B$9=F1),C$1:C$9,,0,-1)
The -1 indicates we want to search bottom-up.
Without ExcelO365, you could maybe use boolean logic too:
=LOOKUP(2,1/((A$1:A$9=E1)*(B$1:B$9=F1)),C$1:C$9)
Or:
=INDEX(C:C,MAX((A$1:A$9=E1)*(B$1:B$9=F1)*ROW(A$1:A$9)))
and confirm through CSE.
You can try AGGREGATE() function.
=IFERROR(INDEX($C$1:$C$10,AGGREGATE(14,6,ROW($1:$10)/(($A$1:$A$10=E2)*($B$1:$B$10=F2)),1)),"")
