# Return MAX date based on two criterias

1. ## 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?

2. ## Re: Return MAX date based on two criterias

Hi cedequ,
Perhaps this

``Please Login or Register  to view this content.``
Control + Shift + Enter to Array it.

3. ## Re: Return MAX date based on two criterias

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?

Originally Posted by wenqq3
Hi cedequ,
Perhaps this

``Please Login or Register  to view this content.``
Control + Shift + Enter to Array it.

4. ## Re: Return MAX date based on two criterias

Hi cedequ,

Which version of Excel is this for?

Regards

5. ## Re: Return MAX date based on two criterias

Try

Formula:
`Please Login or Register  to view this content.`

6. ## Re: Return MAX date based on two criterias

Two non-array solutions:

For 2010 or later:

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

For all versions:

=MAX(INDEX((B1:B100="string1")*A1:A100,,))

Regards

7. ## Re: Return MAX date based on two criterias

I think Fotis1991's answer is that one you looking for.

8. ## Re: Return MAX date based on two criterias

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.

9. ## Re: Return MAX date based on two criterias

Point is that you found your solution! So:

You are welcome and thanks for the feed back.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

There are currently 1 users browsing this thread. (0 members and 1 guests)