Formula to Pull Last Contact Date for each Contact in Call Log

[Tweet2Quit]

We are looking for help creating a formula to find the last time a specific contact from the call log has been called and return this information in a column (Last Contact) on a different sheet called Participant Info. I've attached the spreadsheet. Very new to excel formulas, any help would be appreciated.

[DBY]

Hello
This array formula (entered with Ctrl+Shift+Enter) will bring back the last date and time of the selected client:

Please Login or Register  to view this content.

if entered correctly you will see curly brackets {} around the formula.

DBY

[Tony Valko]

In your file the dates are sorted in ascending order so the last time is the bottom-most instance, right?

Try this...

Data Range

	A	B	C	D	E	F	G
1	Name	Date	Time	------	Name	Date	Time
2	Client1	6/1/2016	9:56 AM		Client1	6/6/2016	2:03 PM
3	Client2	6/2/2016	2:53 PM
4	Client1	6/3/2016	12:25 PM
5	Client3	6/4/2016	9:16 PM
6	Client2	6/5/2016	1:51 PM
7	Client1	6/6/2016	2:03 PM
8	Client3	6/7/2016	7:24 PM
9	Client4	6/8/2016	9:42 AM
10	Client2	6/9/2016	12:19 PM
11	Client3	6/10/2016	12:37 PM

This formula entered in F2:

=LOOKUP(2,1/($A$2:$A$11=$E$2),B$2:B$11)

Copy across to G2.

Format F2 as Date and G2 as Time.

[Tweet2Quit]

Thank you very much! I do see the curly brackets, however it gives me a #NAME? error. I entered the formula just as you provided it.

[DBY]

Have you defined the 'Client' that is the variable that you input into the formula? Alternatively, try Tony's solution if that's easier.

Please Login or Register  to view this content.

[Tweet2Quit]

I'm not sure how to define 'Client' here. Any tutorial or help you can provide is much appreciated.

[Tweet2Quit]

I got the formula to pull the latest date that anyone was contacted, however not the latest date an individual was contacted. Thoughts?

[DBY]

The "Client" can be a cell reference, for example here it's C7:

Please Login or Register  to view this content.

Or it could be 'Hard Coded':

Please Login or Register  to view this content.

You can have a list of names and drag the formula down for each one or maybe a drop down list of them in a cell. When the drop down is changed the returned value will change accordingly.

Hope this helps.
DBY

Added example file with Array formulas and DMax function.

[Tweet2Quit]

Thank you very much, it looks great. I am trying to adapt your formula to pull a client's phone number from the contact info page, however am not sure if I'm using VLOOKUP correctly.
=VLOOKUP(IF(tblCallLog[Participant]=Client,tblCallLog, 6, TRUE))
That is returning an error and I'm not sure how to proceed.

[DBY]

Hello
With VLOOKUP there's no need to use the IF Function. This will return the Primary Phone number:

Please Login or Register  to view this content.

The client's name here is in K11 on the Call Log sheet or whatever cell you wish to reference. For the secondary phone number you would adjust the Column Index to 6.

DBY