FOrmula to return name of activity of if between 2 dynamic dates

[Darren1988]

I have the attached where we need a dynamic formula which will look at the date of the case ID and then use the company ID to look at any activity made for that company. If theres any activity for that company 2 days prior i need to then pull back the user who created that activity within that time period. Im struggling to bring back the name, i can use a countifs to see how many activities were created within 2 days of that record.

i.e. case id 833216 was received 2/3/22 and for that company there were 2 activities logged within a 2 day period before but were from multiple people so i need it to show multiple, but if there was only 1 person i need it to bring back that name.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Glenn Kennedy]

Two of your expected answers seem to be mixed up:

=IF(B3="","",CHOOSE(1+COUNTIFS(H:H,B3,I:I,">="&C3-2,I:I,"<="&C3),"NA",INDEX($J$3:$J$12,MATCH(1,INDEX(($H$3:$H$12=B3)*($I$3:$I$12>=C3-2)*($I$3:$I$12<=C3),0),0)),"Multiple"))

[Glenn Kennedy]

Please use this, instead... previous version is faulty.

=IF(B3="","",CHOOSE(MIN(3,1+COUNTIFS(H:H,B3,I:I,">="&C3-2,I:I,"<="&C3)),"NA",INDEX($J$3:$J$12,MATCH(1,INDEX(($H$3:$H$12=B3)*($I$3:$I$12>=C3-2)*($I$3:$I$12<=C3),0),0)),"Multiple"))

[Darren1988]

ah thats perfect thank you

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.