SOLVED

# Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desired.

Occasional Contributor

# Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desired.

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.

5 Replies

# Re: Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desire

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.

Best Response confirmed by cuong (Microsoft)
Solution

# Re: Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desire

As an alternate, if you have the most recent release of Excel

=MAX(FILTER(F15:F22,(B15:B22=B8)*(C15:C22=C8)))

# Re: Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desire

That works, Thank you for your assistance

# Re: Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desire

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

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

# Re: Index Match Match delivers multiple responses. Date shown is 1st encountered, MAX date is desire

I have the same issue and this works great!! Thanks for posting.