Checking dates within a range in a lookup

[IncrediBulk]

Hello all

I've been asked to clean up some data in our system and have hit a bit of a snag

We have case files and then case sessions. Each holds an owner field... and they should always match when a case session is created

Case owner Bob, case session owner at creation will be Bob
Case owner changes to John, all new case sessions with have John as the owner

We had a previous bug which did not do this properly and has left hundreds of blank owners in case sessions. I have been able to sort out most of them but now have an issue when the Case Owner has changed multiple times.

I'm looking for a way to compare the creation date on a session against the dates that the Case Owners changed and then picking the correct owner to assign

So for this example from the second image, the expected results would be;

Bob
Mark
Jane
Mark
Cara
Cara

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book2
	A	B	C	D	E	F	G	H	I	J
1	File Number	Date	Old Value Name	New Value Name			int_servicee	createdon	int_filenumber	Name
2	SF0133184	3/2/2021 12:32 PM	John	Jane			SE-1821457	1/6/2021 12:00 AM	SF0149145	Bob
3	SF0149145	18/6/2021 10:15 AM	Bob	Mark			SE-1821458	30/6/2023 12:00 AM	SF0149145	Mark
4	SF0133184	17/8/2021 10:38 AM	Sara	Cara			SE-1821456	24/4/2023 6:26 AM	SF0149145	Jane
5	SF0149145	12/4/2023 10:33 AM	John	Jane			SE-1843246	15/3/2024 12:00 AM	SF0149145	Mark
6	SF0149145	27/6/2023 7:54 AM	Bob	Mark			SE-1843244	11/7/2023 2:06 AM	SF0149145	Mark
7	SF0176237	3/7/2023 9:00 AM	Sara	Cara			SE-1843248	29/10/2024 12:00 AM	SF0149145	Cara
8	SF0176237	7/10/2023 1:29 PM	John	Jane
9	SF0149145	22/12/2023 11:21 AM	Bob	Mark
10	SF0149145	20/6/2024 3:43 AM	Sara	Cara
Sheet5

Cell Formulas
Range	Formula
J2:J7	J2	=XLOOKUP(H2,FILTER(B$2:B$10,A$2:A$10=I2),FILTER(D$2:D$10,A$2:A$10=I2),XLOOKUP(I2,A$2:A$10,C$2:C$10,"??",0),-1,1)

Notice that I got a different result for the second to last line, Please check that.

 

[IncrediBulk]

No you were correct, I got the years mixed up while writing out the expected results. That formula is incredible! I'll give it a try in my file

Thank you!!!!

 

[Eric W]

Here's a slightly shorter version that might be a bit easier to follow:

=LET(t,$A$2:$D$10,d,H2,fn,I2,f,FILTER(t,INDEX(t,0,1)=fn),XLOOKUP(d,INDEX(f,0,2),INDEX(f,0,4),INDEX(f,1,3),-1,1))

Glad to help! Let us know if you need anything else.