Aug 08 2020 01:10 PM
Index match match grabs the first date field even though their may be several associated. I need to tweak formula so that all dates are considered and the latest, newest, or MAX date is returned and advertised in cell.
'=INDEX($F$15:$F$22,MATCH($B8&$C8,$B$15:$B$22&$C$15:$C$22,0))
F Column is date field. B Column is Last Name criteria. C Column is First Name criteria.
Aug 08 2020 01:22 PM
Use:
=MAXIFS($F$15:$F$22,$B$15:$B$22,B8,$C$15:$C$22,C8)
and format the cell with the formula as a date.
Aug 08 2020 02:03 PM
Solution
As an alternate, if you have the most recent release of Excel
=MAX(FILTER(F15:F22,(B15:B22=B8)*(C15:C22=C8)))
Aug 09 2020 05:35 PM
That works, Thank you for your assistance
Aug 09 2020 06:55 PM
You are most welcome.
If the FILTER function is new to you, you might appreciate the instruction available in this YouTube video. https://www.youtube.com/watch?v=9I9DtFOVPIg
Sep 11 2020 04:02 PM
I have the same issue and this works great!! Thanks for posting.
Aug 08 2020 02:03 PM
Solution
As an alternate, if you have the most recent release of Excel
=MAX(FILTER(F15:F22,(B15:B22=B8)*(C15:C22=C8)))