get earliest Date-time and Latest Date-time based on 2 criterias (first & last name)

Sample Data1.jpgSample Data2.jpg

i have 2 screenshots of a dummy data

what i need is to get the Earliest Date-time based on the person's first and last name. this first formula will go to column D.
and another formula that would return the persons Last incident (date-time)

notice that there are multiple incidents created by the same person with unique date-time values. i'm guessing the formula will likely be using the Max function and Vlookup but i couldnt piece it together that returns the right values i need..

so far this is what i've come up: (i)
=MAX(VLOOKUP(and(C2 & "*", B2 & "*",H:J,3,0))
=MIN(VLOOKUP(and(C2 & "*", B2 & "*",H:J,3,0))
but it sadly doesn't work... (they both return the same values) (i modified a bit the colums - added a copy of column G to Column J)

you posted an screenshot, in which forummembers can't work.

So they have to rebuilt you sheet thereself.

Anwer:
Use a pivot table. for that kind of work.

You could use Max(ifs... Min(Ifs.. instead of vlookup, check out for attachment,


Does that help ?

Check the Non Array solution...
though Boopathi's solution are best one..
For Min

Formula:

Please Login or Register  to view this content.

For Max

Formula:

Please Login or Register  to view this content.

Wanna learn how these formula work.. Click the link the below..(Excel-Buzz.blogspot.in)
Check the attachment..