| A | B | C |
1 | Part No | Order | Date |
2 | EC-00004 | 113437 | 04/01/2017 |
3 | Part No | Order | Date |
4 | EC-00005 | 113437 | 03/01/2017 |
5 | EC-00004 | 113437 | 04/01/2017 |
6 | EC-00004 | 111791 | 17/03/2017 |
7 | EC-00006 | 111791 | 12/01/2017 |
8 | EC-00004 | 111793 | 16/02/2017 |
<tbody>
</tbody>
I currently have a table as shown above where I can enter values into "A2" & "B2" with a formulae in cell "C2" as follows:-
=INDEX($C$4:$C$8,MATCH($A$2&$B$2,$A$4:$A$8&$B$4:$B$8,0))
This currently returns the first value in range "C4:C8" which matches both the criteria entered in cells "A2" & "B2".
However I want to modify the formulae so that it returns the latest (highest) date (not first date) in "C4:C8" where:-
“A2” Matches “A4:A8” (As currently)
“B2” Does not Match “B4:B8” (So not equal to, rather than equal to).
So in this example the current formulae returns “04/01/2017” but in the revised formulae it will return "17/03/2017".
I’ve searched for a solution but I seem unable to pull all the information I’ve found into a single formulae that works, so I’m hoping someone out there can help me.