1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by DVD1992, Jan 11, 2019.

  1. DVD1992

    DVD1992 New Member

    Messages:
    3
    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.png upload_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

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    291
    Please provide a sample workbook with some data and expected results. Thc
  3. DVD1992

    DVD1992 New Member

    Messages:
    3
    Here is a sample.

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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)
    Thomas Kuriakose likes this.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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.
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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 )
  7. DVD1992

    DVD1992 New Member

    Messages:
    3
    Thanks Chihiro and Petet. Both solution worked for me

Share This Page