Formula to return a cell value based on criteria

[IIII]

Hi All,

I have this simple assets tracker attached below. What I'm hoping to do here is to auto-populate the name from Column C (Assigned To), into Column I (Reassigned To) when the same device (by serial # in Column B) has been reassigned to a new user. I've tried using the INDEX w/MATCH function but could only get it to work on the first two rows.

In the example below you can see the same device has been passed around 3 times and is currently assigned to the user 'Steve'.

Fake Business Asset Manager.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Asset_Type	Serial_Device_#	Assigned_To	Department	Assigned_Location	Assigned_Date	Unassigned_Date	Reassigned	Reassigned To	Notes	Barcode
2	PC	2734807049	John Doe	Finance	Office	01/01/2023	01/05/2023	Yes	Dave
3	PC	2734807049	Dave	Customer Service	Office	01/05/2023	23/05/2023	Yes	Steve
4	PC	2734807049	Steve	Helpdesk	Home	23/05/2023
Sheet1

Thanks for any help with this and appreciate your time.

Cheers.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Asset_Type	Serial_Device_#	Assigned_To	Department	Assigned_Location	Assigned_Date	Unassigned_Date	Reassigned	Reassigned To	Notes	Barcode
2	PC	2734807049	John Doe	Finance	Office	44927	45047	Yes	Dave
3	PC	2734807049	Dave	Customer Service	Office	45047	45069	Yes	Steve
4	PC	2734807049	Steve	Helpdesk	Home	45069			Steve
Main

Cell Formulas
Range	Formula
I2:I4	I2	=FILTER($C$2:$C$4,($B$2:$B$4=B2)*($F$2:$F$4=G2),C2)

 

[JamesCanale]

Two different ways.

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Asset_Type	Serial_Device_#	Assigned_To	Department	Assigned_Location	Assigned_Date	Unassigned_Date	Reassigned	Reassigned To	Notes	Barcode
2	PC	2734807049	John Doe	Finance	Office	1/1/2023	5/1/2023	Yes	Dave
3	PC	2734807049	Dave	Customer Service	Office	5/1/2023	5/23/2023	Yes	Steve
4	PC	2734807049	Steve	Helpdesk	Home	5/23/2023		Yes	Harry
5	PC	123456	Fred	Helpdesk	Office	5/24/2023		Yes	Jamie
6	PC	2734807049	Harry	Helpdesk	Home	5/25/2023		Yes	Sally
7	PC	123456	Jamie	Helpdesk	Office	5/26/2023		No
8	PC	2734807049	Sally	Helpdesk	Office	5/27/2023		Yes	Mary
9	PC	2734807049	Mary	Helpdesk	Home	5/28/2023
10
11
12
13	Asset_Type	Serial_Device_#	Assigned_To	Department	Assigned_Location	Assigned_Date	Unassigned_Date	Reassigned	Reassigned To	Notes	Barcode
14	PC	2734807049	John Doe	Finance	Office	1/1/2023	5/1/2023	Yes	Dave
15	PC	2734807049	Dave	Customer Service	Office	5/1/2023	5/23/2023	Yes	Steve
16	PC	2734807049	Steve	Helpdesk	Home	5/23/2023	5/23/2023	Yes	Steve
17	PC	123456	Fred	Helpdesk	Office	5/24/2023	5/24/2023	Yes	Fred
18	PC	2734807049	Harry	Helpdesk	Home	5/25/2023	5/26/2023	Yes	Sally
19	PC	123456	Jamie	Helpdesk	Office	5/26/2023
20	PC	2734807049	Sally	Helpdesk	Office	5/27/2023	5/28/2023	Yes	Mary
21	PC	2734807049	Mary	Helpdesk	Home	5/28/2023
Sheet17

Cell Formulas
Range	Formula
F5:F9,F17:F21	F5	=F4+1
I2:I8	I2	=IF(H2="Yes",XLOOKUP(B2,B3:B$9,C3:C$9,"",0),"")
I9	I9	=IF(H9="Yes",XLOOKUP(B9,B$9:B10,C$9:C10,"",0),"")
I14:I21	I14	=IF([@Reassigned]="Yes",INDEX(SORT(FILTER(Table6[[Assigned_To]:[Assigned_Date]],([Assigned_Date]>=[@[Unassigned_Date]])*([Serial_Device_'#]=[@[Serial_Device_'#]])),4),1,1),"")

 

[IIII]

Thanks @Fluff and @JamesCanale for taking the time to respond.

@JamesCanale - your 2nd solution (Index/Sort etc.) hit the nail on the head! Thanks mate.

Appreciate the help everyone.

Cheers.