Function/Formula for connecting dates to names to a list

[Chandler8]

Hey guys, I could use some type of formula to grab the dates from a list that connect with each persons name on Guest_Info and bring it to Guest_List. The idea is each time their name is typed and they associate with a date; it will automate into these cells (Check_In_1, Check_Out_1 etc.)
The backstory is this company only allows a person two maximum stays in a lifetime so I have to monitor that. Here is some pictures below! Thank you.
Cells C4,D4,E4 and F4 are the cells I need the formula in.

Rental Arrivals.xlsm
	A	B	C	D	E	F	G	H	I	J
3	Check_In	Check_Out	#_of_Days	Property	Lease_Holder	Registered_Guest1	Registered_Guest2	Registered_Guest3	Registered_Guest4	Registered_Guest5
4	5/19/2022	5/21/2022	2	R Hart: 152 Island Drive, Unit B	Catherine Frye	Catherine Frye	Rick Jones
5	5/21/2022	5/22/2022	1	Leary: 170 Loch Dornie, Unit E	Greg Proctor	Greg Proctor	Trina Proctor	Mark Poovey	Sharon Poovey
6	5/26/2022	5/30/2022	4	Laskin: 1236 Mountain Springs	Neal Blinde	Neal Blinde	Jessica Blinde	Arden Blinde	Norah Blinde	Graham Blinde
7	5/26/2022	5/31/2022	5	L. Hart: 934 Hillside Drive	Stacie Menichella	Stacie Menichella	Dan Menichella	Bryce Menichella	Brandon Artiqua	Lauren Artiqua
8	5/27/2022	5/30/2022	3	Belk: 987 Mountain Springs	Dave Powell	Dave Powell	Meredith Powell	Porter Powell	Rivers Powell	Ella Powell
9	5/27/2022	5/30/2022	3	Young: 282 North Wildflower	David Taylor	David Taylor	Elizabeth Taylor	Broughton Taylor	William Taylor
10	6/17/2022	6/19/2022	2	Sewell: 210 Loch Dornie, Unit B	Michael Patton	Michael Patton	Amy Patton
11	6/18/2022	7/1/2022	13	Laskin: 1236 Mountain Springs	Anne Warren	Anne Warren	Ellie Waren	Timothy Warren	Claire Warren	Jeff Warren
12	6/19/2022	6/22/2022	3	Porterfield: 161 Mountain View	Patrick Walsh	Patrick Walsh	Emily Walsh	Michael Walsh	Martha Walsh	Patrick Jr. Walsh
13	6/22/2022	6/26/2022	4	Leary: 170 Loch Dornie, Unit E	Mark Frisch	Mark Frisch
14	6/23/2022	6/26/2022	3	Porterfield: 161 Mountain View	Anne McGowan	Cam Findlay	Amy Findlay	David Findlay	Susan Findlay	Kathryn Beiser
15	6/23/2022	6/30/2022	7	L Hart: 934 Hillside Drive	Hans Stig Moller	Hans Stig Moller	Nancy Stig Moller	Catherine Stig Moller	Rita Stig Moller	Jens Stig Moller
Guest_Info

Cell Formulas
Range	Formula
C5:C15	C5	=(B5-A5)

Rental Arrivals.xlsm
	A	B	C	D	E	F
3	Guest_Name	Guest_Last_Name	Check_In_1	Check_Out_1	Check_In_2	Check_Out_2
4		#VALUE!
5		#VALUE!
6		#VALUE!
7		#VALUE!
8		#VALUE!
9		#VALUE!
10		#VALUE!
11		#VALUE!
12		#VALUE!
13		#VALUE!
14		#VALUE!
15		#VALUE!
Guest_List

Cell Formulas
Range	Formula
B4:B15	B4	=RIGHT([@[Guest_Name]],LEN([@[Guest_Name]])-FIND(" ",[@[Guest_Name]]))

 

[Fluff]

Duplicate to: Formula for connecting a persons name to several dates to show how many times they have stayed.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.