Return MAX date based on two criterias

Hi there,

did my investigation, but not success so far. Here's the scenario:

Both Col-A & Col-B can be filled in or not. If Col-B is filled, Col-A is as well.

Within Col-C, I need to retrieve the MAX date from Col-A, for that related "string" (and not for the empty cell). To do so, I need obviously to exclude (ie: using isnumber) the empty fields...

Col-A Col-B Col-C
empty empty
1.2.2014 string1 1.8.2016
empty empty
1.9.2015 string2 1.8.2018
1.8.2018 string2 1.8.2018
empty empty
1.8.2016 string1 1.8.2016

I tried, sumproduct in conjunction with max, but can only retrieve the row reference, not the value itself... otherwise, it returns #values...

Any ideas from your expert views?

Thanks in advance!

Hi there,
I already went through this solution, it works great (thank you), however I must avoid any array solutions (CES).
Any alternate path to get this resolved, WITHOUT array type of solution?
Thanks in advance for your support, much appreciated.

Originally Posted by wenqq3

Hi cedequ,
Perhaps this

Please Login or Register  to view this content.

Control + Shift + Enter to Array it.

And the winner is: XOR LX

Thanks for suggesting the usage of aggregate (as I am using excel 2010), as this one is very handy to skip cell returning errors.

I ended up, using the second parameter of aggregate (= 7), to discard errors:

=AGGREGATE(14,7,(B1:B100="string1")*A1:A100,1)

Other options listed above, requires that the scope does not contain empty / error cells, which I mentioned that I must address.

May all of you (depending where you are), have a great week-end.