CODEWITHSUNDEEP
excelindexingexcel-formulamaxmatch
Legal Centre
Legal Centre

Reputation: 3

EXCEL: Index Match Max date Multiple Criteria

This might have been solved already, but I am really struggling for days now... I am working on a client list. Multiple staff were in charge of different follow up for each client. However we are trying to allocate a unique staff to each client. We decided that the staff who did the last follow up would be in charge of the client. Therefore I need for each client (ID) line by line to see the max value according to (Date of follow up) and match with the correct staff (staff who did the follow up). What I should find is the column H, done manually in the example below, based on the last follow up. All the index match, with max value I tried only show for all clients CW3 as it is the last follow up for all clients.

Click on the table to see the columns details before

I cannot upload images yet

Upvotes: 0

Views: 742

Answers (2)

JvdV
JvdV

Reputation: 75840

What I used in D2:

enter image description here

=LOOKUP(2,1/(A:A=A2),C:C)

Also, it's always better to be more specific about ranges instead of whole column references.

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36860

If you have Excel365 then it would be easier for you to get desired result. Use below formula-

=INDEX(SORT(FILTER($B$2:$C$8,$A$2:$A$8=A2,""),1,-1),1,2)

enter image description here

Upvotes: 0

Related Questions