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

1. 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)

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

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.

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

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

Does that help ?

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

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..

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1