Would like to index different data points based on multiple criteria, and then index additional data in the same columns based on date/time proximity

[freeb1893]

Here is a sample data set:

ISSUENUM	CHANGE DATE	FIELD CHANGED	OLDSTRING	NEWSTRING
13283	3/28/2022 1:23 PM​	assignee		Doe, John
13293	3/29/2022 2:07 PM​	assignee	Doe, John	Doe, Jane
13293	3/30/2022 8:41 AM​	assignee	Doe, Jane
13283	4/4/2022 8:35 PM​	assignee	Doe, John
13283	5/26/2022 10:16 AM​	status	Open	Cancelled
13901	6/27/2022 3:43 PM​	priority	Critical	Trivial
13904	6/28/2022 7:49 AM​	assignee		Akwaa, Joseph

I want to index the above data based on matching criteria, and then do additional indexing based on matching criteria and identifying the record with the closest Change Date to my prior indexed data.

For example, I want to index the above to find the Change Date for when Field Changed is "status" and the OldString was "Ready to Develop" or all unique IssueNum, and then furthermore in an adjacent cell result cell I want to index the same data to find the Change Date that is closest in time to the previous indexed Change Date for "status" change, but rather where the Field Changed is "assignee", and then in another adjacent cell result I want to index the NewString based on the Change Date that is closest in time to the previous indexed Change Date for "status" change and the Field Changed is "assignee".

Anyone know how to put that into a formula? Thanks in advance

 

[Skybluekid]

What version are you using?

 

[freeb1893]

What version are you using?

Version 2208 (Build 15601.20680_) Micosoft 365 Apps

 

[Fluff]

Version 2208 (Build 15601.20680_) Micosoft 365 Apps

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[freeb1893]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sounds good. Just added that to my Account Details. Beyond that, any thoughts on how to put my original question / logic into an Excel formula?