SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1575130%22%20slang%3D%22en-US%22%3EIndex%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desired.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575130%22%20slang%3D%22en-US%22%3E%3CP%3EIndex%20match%20match%20grabs%20the%20first%20date%20field%20even%20though%20their%20may%20be%20several%20associated.%20I%20need%20to%20tweak%20formula%20so%20that%20all%20dates%20are%20considered%20and%20the%20latest%2C%20newest%2C%20or%20MAX%20date%20is%20returned%20and%20advertised%20in%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3E'%3DINDEX(%24F%2415%3A%24F%2422%2CMATCH(%24B8%26amp%3B%24C8%2C%24B%2415%3A%24B%2422%26amp%3B%24C%2415%3A%24C%2422%2C0))%3C%2FP%3E%3CP%3EF%20Column%20is%20date%20field.%20B%20Column%20is%20Last%20Name%20criteria.%26nbsp%3BC%20Column%20is%20First%20Name%20criteria.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1575130%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575151%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desire%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740274%22%20target%3D%22_blank%22%3E%40GOKY20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMAXIFS(%24F%2415%3A%24F%2422%2C%24B%2415%3A%24B%2422%2CB8%2C%24C%2415%3A%24C%2422%2CC8)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20format%20the%20cell%20with%20the%20formula%20as%20a%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575217%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desire%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740274%22%20target%3D%22_blank%22%3E%40GOKY20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20alternate%2C%20if%20you%20have%20the%20most%20recent%20release%20of%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMAX(FILTER(F15%3AF22%2C(B15%3AB22%3DB8)*(C15%3AC22%3DC8)))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576979%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desire%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works%2C%20Thank%20you%20for%20your%20assistance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1577032%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desire%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1577032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754163%22%20target%3D%22_blank%22%3E%40GKOGOKY%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20most%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20FILTER%20function%20is%20new%20to%26nbsp%3B%20you%2C%20you%20might%20appreciate%20the%20instruction%20available%20in%20this%20YouTube%20video.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1659696%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20Match%20delivers%20multiple%20responses.%20Date%20shown%20is%201st%20encountered%2C%20MAX%20date%20is%20desire%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1659696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20same%20issue%20and%20this%20works%20great!!%20Thanks%20for%20posting.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted

@GOKY20 

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.

Highlighted
Best Response confirmed by cuong (Microsoft)
Solution

@GOKY20 

 

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

 

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

 

 

Highlighted
Highlighted

@GKOGOKY 

 

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

@mathetes 

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