# Find Name and return latest Date

#### DVD1992

##### New Member
What i'm trying to do is Find a name of one person in a column 100's of name with the name showing up more than 10 returning the date based on the row numbers excluding "0" from that array returning the latest date for that name.

Column B = Date/Time
Column C = Names
E2 = Search Value (name)

Was going to use LARGE to get the latest date from the array

=INDEX(B:B,((C:C=E2)*(ROW(C:C)))) < provided me the row number of names

It doesn't seem to bring back the proper date to row number..

Any help would be appreciated.
Thanks

#### Attachments

• 4.4 KB Views: 6
• 4 KB Views: 6

#### pecoflyer

##### Active Member
Please provide a sample workbook with some data and expected results. Thc

#### DVD1992

##### New Member
Here is a sample.

#### Attachments

• 11.6 KB Views: 5

#### Chihiro

##### Excel Ninja
Shouldn't result be 1/9/19 6:15 PM?

At any rate, try following.
=AGGREGATE(14,6,(\$B\$2:\$B\$67)/(\$C\$2:\$C\$67=E2),1)

#### Peter Bartholomew

##### Well-Known Member
This is a question for which the preferred solution is changing by the month.
AGGREGATE implements the MAX function as an array formula but neither fact is overtly visible.

= MAX( IF( Table1[Name]=Name, Table1[Date and Time] ) )
is readable but requires CSE if it is to give a result.

The array part of the calculation may be implemented as a Named Formula filteredDate: = IF( Table1[Name]=Name, Table1[Date and Time] )
leaving the worksheet calculation as
= MAX( filteredDate ).
That works without CSE but most Excel users will fail to find to definition of 'filteredDate' in Name Manager. The MAX( IF( …, … ) ) strongly suggests the Office 2016 solution

= MAXIFS( Table1[Date and Time], Table1[Name], Name )

Then again the name 'filteredDate' is a harbinger of the future dynamic arrays in which the FILTER function will allow
= MAX( FILTER( Table1[Date and Time], Table1[Name]=Name, "Not found" ) )
which does somewhat underline what an unintelligible mess traditional spreadsheet development is and just how challenging it is to master.

Now we have a clear and readable solution but, as yet, it is available to hardly anyone [Office 365 Insider fast].

p.s. Any or all of the above formulae may be further obscured by replacing structured references with direct (coordinate) notation.

#### Peter Bartholomew

##### Well-Known Member
p.p.s. As it stands, the dataset is ordered from recent to old so the first occurrence of "Elsy Toon" is necessarily the latest

= INDEX( Table1[Date and Time], MATCH( Name, Table1[Name], 0 ) )

If you are really perverse, you might even like

= VLOOKUP( Name, IF( {0,1}, Table1[Date and Time], Table1[Name] ), 2, FALSE )

#### DVD1992

##### New Member
Thanks Chihiro and Petet. Both solution worked for me