Complex INDEX MATCH - Now Add Max

[horizonflame]

Hi All

I have a couple of Index Match formulas that are working well for me but I've realised there may be more than one result to find and I only want the latest date.

My formulas sometimes have two match criteria and two index location, but I'm not sure how to return the highest valued match?

=IFERROR(INDEX(GATED!$H$3:$H$1000,MATCH(1,INDEX(("Completed EUT Approved"=GATED!$F$3:$F$1000)*($B5=GATED!$J$3:$J$1000),0,1),0)),IFERROR(INDEX(BLOCK!$H$3:$H$1000,MATCH(1,INDEX(("Completed EUT Approved"=BLOCK!$F$3:$F$1000)*($B5=BLOCK!$J$3:$J$1000),0,1),0)),IFERROR(INDEX('IW66'!$H$3:$H$1000,MATCH(1,INDEX(("Completed EUT Approved"='IW66'!$F$3:$F$1000)*($B5='IW66'!$J$3:$J$1000),0,1),0)),"")))

May thanks

 

[steve the fish]

This may work. It depends what the IFERROR is meant to be trapping.

=IF(COUNTIFS(GATED!$F$3:$F$1000,"Completed EUT Approved",GATED!$J$3:$J$1000,B5),MAXIFS(GATED!$H$3:$H$1000,GATED!$F$3:$F$1000,"Completed EUT Approved",GATED!$J$3:$J$1000,B5),IF(COUNTIFS(BLOCK!$F$3:$F$1000,"Completed EUT Approved",BLOCK!$J$3:$J$1000,B5),MAXIFS(BLOCK!$H$3:$H$1000,BLOCK!$F$3:$F$1000,"Completed EUT Approved",BLOCK!$J$3:$J$1000,B5),IF(COUNTIFS('IW66'!$F$3:$F$1000,"Completed EUT Approved",'IW66'!$J$3:$J$1000,B5),MAXIFS('IW66'!$H$3:$H$1000,'IW66'!$F$3:$F$1000,"Completed EUT Approved",'IW66'!$J$3:$J$1000,B5),"")))

 

[horizonflame]

@steve the fish, thank you. The IFERROR is changing the non=match #N/A to blank cell.

 

[horizonflame]

Apologies, I'm using Excel 2012 so don't have MAX IFS