Excel Lookup

New Contributor

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

2 Replies

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

 

@Detlef Lewin

Thanks, Detlef. Will check this out.