Forum Discussion

SurenBharadwaj's avatar
SurenBharadwaj
Copper Contributor
Jun 29, 2021

Excel Lookup

Hi,

 

I have a table with multiple columns. One of these column contains names. Another contains dates. I have used the ISNUMBER search option along with the MAX function to find the latest date for a specified name in the table from the name column. The formula is as below.

=MAX(IF(ISNUMBER(SEARCH("Aaaa",Sheet1!$I$2:$I$500)),Sheet1!$J$2:$J$500)).

What I want to do now is return the value of another column in the same row from which the highest date value has been returned. Is this possible? If so, how?

 

Regards,
Suren

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    SurenBharadwaj 

    NameDateAnother Column NameDateAnother Column
    Peter06.05.2012York Frank15.10.2019Paris
    Frank15.10.2019Paris    
    Jack23.01.2023Home    
    Frank01.12.2013Night    
    Frank17.04.2017Ship    
    Peter23.04.2015Horse    
    Jack06.10.2012Hammer    

     

     

    =MAXIFS($B$2:$B$8,$A$2:$A$8,F2)
    =FILTER($C$2:$C$8,($A$2:$A$8=F2)*($B$2:$B$8=G2))

     

Resources