• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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


upload_2019-1-11_7-48-59.pngupload_2019-1-11_7-54-11.png

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

Any help would be appreciated.
Thanks
 

Attachments

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 )
 
Top