Get Data from last 3 cells with values in 1 column

[MrDB4Excel]

See attached Excel Mini Sheet which contains the following:
When an action concludes then I copy the 3 values that represent the concluded action, in this case, in column "C", rows 16, 17, & 18, and then paste that into column "C", rows 19, 20, & 21.
Presently, as you view this sheet, you see that the corresponding row values in column "F" do not show correctly because new values have not yet been placed in column "C", rows 19, 20, & 21.
What I would like to do is have the values in row 2, columns "H", "I", & "J" be dynamic such that when new data is entered in column "C" that is relevant to the action beginning anew that these values in H2, I2, & J2 update to the next level; in this case, they would update to 6a, 6b, & 6c. As you can see, the formulas in H3, I3, & J3 would auto-update to new values.
Is this possible with only formula and not any VBA?
Any help is much appreciated.

Previous-Current.xlsx
	A	B	C	D	E	F	G	H	I	J
1			Column C			Data From Column C		#a of PCD Action Beginning Date, or Previous	#b of PCD Current Day, Shows Relevant to =TODAY()	#c of PCD Duration
2								5a	5b	5c
3	#	PCD	Action 1		Number	Value		May/24/2021	Jun/15/2021	22 Days
4	1a	Previous	Jun/09/2020		1a	Jun/09/2020
5	1b	Current	Aug/28/2020		1b	Aug/28/2020		When an action concludes then I copy the 3 values that represent the concluded action, in this case, in column "C", rows 16, 17, & 18, and then paste that into column "C", rows 19, 20, & 21. Presently, as you view this sheet, you see that the corresponding row values in column "F" do not show correctly because new values have not yet been placed in column "C", rows 19, 20, & 21. What I would like to do is have the values in row 2, columns "H", "I", & "J" be dynamic such that when new data is entered in column "C" that is relevant to the action beginning anew that these values in H2, I2, & J2 update to the next level; in this case, they would update to 6a, 6b, & 6c. As you can see, the formulas in H3, I3, & J3 would auto-update to new values. Is this possible with only formula and not any VBA?
6	1c	Duration	80 Days		1c	80 Days
7	2a	Previous	Aug/28/2020		2a	Aug/28/2020
8	2b	Current	Nov/19/2020		2b	Nov/19/2020
9	2c	Duration	83 Days		2c	83 Days
10	3a	Previous	Jan/25/2021		3a	Jan/25/2021
11	3b	Current	Apr/13/2021		3b	Apr/13/2021
12	3c	Duration	78 Days		3c	78 Days
13	4a	Previous	Apr/13/2021		4a	Apr/13/2021
14	4b	Current	May/24/2021		4b	May/24/2021
15	4c	Duration	41 Days		4c	41 Days
16	5a	Previous	May/24/2021		5a	May/24/2021
17	5b	Current	Jun/15/2021		5b	Jun/15/2021
18	5c	Duration	22 Days		5c	22 Days
19	6a	Previous			6a	Jan/00/1900
20	6b	Current			6b	Jan/00/1900
21	6c	Duration	0 Days		6c	0 Days
22	7a	Previous			7a
23	7b	Current			7b
24	7c	Duration	0 Days		7c
Test Example

Cell Formulas
Range	Formula
H3	H3	=XLOOKUP(H2,E3:E23,F3:F23,0)
I3	I3	=XLOOKUP(I2,E4:E24,F4:F24,0)
J3	J3	=XLOOKUP(J2,E5:E25,F5:F25,0)
C6,C24,C21,C18,C15,C12,C9	C6	=SUM(C5-C4)&" Days"
C17	C17	=TODAY()
F4:F21	F4	=VLOOKUP(E4,PCD,3,TRUE)

Named Ranges
Name	Refers To	Cells
'Test Example'!PCD	='Test Example'!$A$3:$C$21	F4:F21

 

[Peter_SSs]

I'm not following, but I am also confused as your profile says Excel 2013 but your mini-sheet is using XLOOKUP, only available in Microsoft 365. So, apart from clarifying your question, does your profile need updating?

 

[MrDB4Excel]

Yes, I am sure it is confusing, but I assure you XLOOKUP works in my 2013 Excel.
My profile is correct.
Perhaps because of an addin, but I have used it for quite some time. I seem to recall adding an addin some time ago.
So take it from there, XLOOKUP is alive and well in my 2013 Excel.
So what is it you are not following? I was certain my explanation was sufficient. What else is needed?
The key notion here is that I copy cell content from, in this example, C16, C17, & C18. Then paste that content into C19, C20, & C21. This then populates or rather fulfills the requirements of the formulas that exist in F19, F20, & F21.
What needs to happen is the 3 cells H2, I2, & J2 need to dynamically update to the content shown in E19, E20, & E21 as a result of new data coming into F19, F20, & F21.

 

[Peter_SSs]

Try
H2: =LOOKUP("Z",F4:F24,E2:E22)
I2: =LOOKUP("Z",F4:F24,E3:E23)
J2: =LOOKUP("Z",F4:F24,E4:E24)

XLOOKUP works in my 2013 Excel.

Perhaps because of an addin,

If that is the case you will need to be careful of any existing XLOOKUP formulas if you ever change to Microsoft 365.

 

[MrDB4Excel]

Try
H2: =LOOKUP("Z",F4:F24,E2:E22)
I2: =LOOKUP("Z",F4:F24,E3:E23)
J2: =LOOKUP("Z",F4:F24,E4:E24)


If that is the case you will need to be careful of any existing XLOOKUP formulas if you ever change to Microsoft 365.

Beautiful, works fantastic, thank you so much.
And as a final note, I have no worries about having issues with 365 because I am fairly certain at my age that I will retain 2013 Excel for years to come.
There exists a remote possibility if I ever acquire a newer PC that I would more than likely end up with 2019 Excel but never will I pay monthly for a 365 subscription.

 

[Peter_SSs]

Beautiful, works fantastic, thank you so much.

You're welcome. Glad it worked for you.