Index Match to return a maximum value not equal to a set criteria.

[roubles]

EC-00004	113437	04/01/2017
EC-00005	113437	03/01/2017
EC-00004	113437	04/01/2017
EC-00004	111791	17/03/2017
EC-00006	111791	12/01/2017
EC-00004	111793	16/02/2017

<tbody>
[TD="align: center"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]

[TD="align: center"]1
[/TD]
[TD="align: center"]Part No
[/TD]
[TD="align: center"]Order
[/TD]
[TD="align: center"]Date
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"]Part No
[/TD]
[TD="align: center"]Order
[/TD]
[TD="align: center"]Date
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: center"]6
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: center"]8
[/TD]

</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.

 

[AlanY]

try

Excel 2012
	A	B	C
1	Part No	Order	Date
2	EC-00004	113437	17/03/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
Sheet3

Cell Formulas
Range	Formula
C2	{=MAX(IF(A4:A8=A2,IF(B4:B8<>B2,C4:C8)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[roubles]

Brilliant..... thanks for your help, guess I was over complicating a bit with my original approach to the problem.

 

[AlanY]

you're welcome