2 Criteria, then find Max date

[DashboardNovice]

In the attached file, I want to set up a formula in the orange cell. I want to use the values in columns C and D as criteria.

The data set is in columns M:O.

I am trying to use something similar to SUMIFS (where employee name and month ending date are the criteria), but instead of summing values in column N, I would like to return the max date in column N (the yellow cell).

I thought about using the OFFSET or INDEX/MATCH functions but I don't see how since I have more than one criteria.

 

[Nebu]

Hi:

Find the attached.
array formula, execute by pressing control+shift+enter key

=MAX(IF(($C$4=$O$3:$O$20)*($D$4=$M$3:$M$20),$N$3:$N$20))

Thanks

 

[Kavfam]

Hi:

Find the attached.
array formula, execute by pressing control+shift+enter key

=MAX(IF(($C$4=$O$3:$O$20)*($D$4=$M$3:$M$20),$N$3:$N$20))

Thanks

I had the same thing: =MAX(IF(M3:M20=D4,IF(O3:O20=C4,N3:N20))) but in a different syntax. Can you explain the IF( (cond1) * (cond2)) syntax?

 

[Nebu]

@Kavfam "*" is for and condition and "+" is for or condition

 

[Kavfam]

Thanks Nebu

 

[DashboardNovice]

Hi:

Find the attached.
array formula, execute by pressing control+shift+enter key

=MAX(IF(($C$4=$O$3:$O$20)*($D$4=$M$3:$M$20),$N$3:$N$20))

Thanks

Thank you Nebu. This is one of the best solutions I have seen. I think I will be able to use this on many occasions. This worked just fine.