Select Max Date Based on Multiple Criteria with Wildcard Text

**TxNyCa** · 02-27-2013, 08:30 PM

I am trying to pull the most recent "date" (column A) in the past if the "activity type" was meeting (column B) and the "participants" included *bob (column C), but bob is in a string of text, not always at the beginning, middle or included at all. I put them in descending order by date for ease of reference, but the dates are random in the report.The formula should ignore dates in the future.

Date: Most recent in past
Activity: =Meeting
Participant: Contains *Bob

The answer should be 1/1/13:
Date Activity Participants
1/1/2010 Call Jill; Bob; Mike
12/1/2012 Meeting Joe
1/1/2013 Meeting Bob; Joe; Jim
2/1/2013 Call Jim; Bob
3/15/2013 Call Julie; Bob
4/15/2013 Meeting Mike, Michelle; Bob
5/1/2013 Call Jill, Bob
6/1/2015 Meeting Mike; Bob

**daddylonglegs** · 02-27-2013, 08:44 PM

Try an array formula like this

=MAX(IF(A2:A100<=TODAY(),IF(B2:B100="Meeting",IF(ISNUMBER(SEARCH("Bob",C2:C100)),A2:A100))))

confirmed with CTRL+SHIFT+ENTER

**TxNyCa** · 02-27-2013, 08:53 PM

Perfect. Thank you!!

**TxNyCa** · 02-28-2013, 12:44 PM

Two follow-up questions:
1. Can this be done not using the array function (C+S+E)?
2. If there are no meetings/calls that meet the criteria, it returns 1/0/1900. I cannot figure out where to add "NA" in the string if false, or there are no matches.

**daddylonglegs** · 02-28-2013, 02:34 PM

You can add an INDEX function and change the formula syntax slightly so you don't need CSE, i.e.

=MAX(INDEX((A2:A100<=TODAY())*(B2:B100="Meeting")*ISNUMBER(SEARCH("Dave",C2:C100))*A2:A100,0))

[not so easy to do for MIN, though]

When you get 1/0/1900 that's just zero formatted as m/d/yyyy - if you change the format to a custom format of m/d/yyyy;; then it will display as blank....or a custom format of m/d/yyyy;;"NA" will display NA (although the actual cell value will still be zero)