Formula needed to transfer figures from one sheet to another

[pedro-egoli]

I have a workbook wih 2 worksheets and the source sheet date (sheet 2) only changes intermittently whereas on recipient sheet (sheet 1) changes daily
Sheet 1 looks like this

Book1
	A	B	C	D	E	F	G	H	I
3	19/06/2020	20/06/2020	21/06/2020	22/06/2020	23/06/2020	24/06/2020	25/06/2020	26/06/2020
4	$53,070	$42,479	$42,479	$42,479	$42,479	$42,479	$42,479	$42,479
5	$53,070	$53,070	$53,070	$53,070	$53,070	$53,070	$42,479	$42,479	needed
Sheet1

Cell Formulas
Range	Formula
A4	A4	=Sheet2!C5
B4:H4	B4	=LOOKUP(99^99,Sheet2!$C:$C)

And sheet 2

Book1
	A	B	C
2	$647,590
3
4	15/06/2020	$692,324	$44,734
5	19/06/2020	$700,660	$53,070
6	25/06/2020	$690,069	$42,479
Sheet2

Cell Formulas
Range	Formula
C4:C6	C4	=B4-$A$2

Any help in providing a formula that will get the result needed in sheet 1 appreciated.
I experimented with formula as you can see without success.

 

[Fluff]

How about, in A4 copied right
=INDEX(Sheet2!$C$4:$C$10,MATCH(A$3,Sheet2!$A$4:$A$10,1))

 

[pedro-egoli]

Thank you fluff works perfectly.
Much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.

 

[pedro-egoli]

Fluff I need your assistance again.
I tried to construct an Index/match formula to transfer other details from one sheet to another based on above formula provided by Fluff (which works as intended) however, I have run into trouble.
Situation is that I have a sheet “Accountbalance” part of which contains the data I need to transfer to sheet called “Daily balances”.
“Accountbalance” is imported from the web in .CSV format.
This is what I have used

Account balances for centrelink Final Version-1.xlsm
	BN
3	28/06/2020
4	$301,408
5	$87,204
6	$12,780
Daily balances

Cell Formulas
Range	Formula
BN5	BN5	=IF(BN3="","",INDEX(AccountBalance!$D$2:$D$3,MATCH(BN$3,AccountBalance!$G$1,1)))
BN6	BN6	=IF(BN3="","",INDEX(AccountBalance!$D$3,MATCH(BN$3,AccountBalance!$G$1,1)))

On "Accountbalance" sheet I have

Account balances for centrelink Final Version-1.xlsm
	D	E	F	G
1	available balance			28/06/2020
2	87203.62
3	12780.37
AccountBalance

The problem is that when I change date in G1 to TODAYS date (29/6/20) this is what happens on sheet "Daily balances"

Account balances for centrelink Final Version-1.xlsm
	BN	BO
3	28/06/2020	29/06/2020
4
5	#N/A	$87,204
6	#N/A	$12,780
Daily balances

Cell Formulas
Range	Formula
BN5:BO5	BN5	=IF(BN3="","",INDEX(AccountBalance!$D$2:$D$3,MATCH(BN$3,AccountBalance!$G$1,1)))
BN6:BO6	BN6	=IF(BN3="","",INDEX(AccountBalance!$D$3,MATCH(BN$3,AccountBalance!$G$1,1)))

Is there a way to have date change in G1 yet retain the value as at each individual day, Say incorporate a Paste Special /Value?
Any assistance appreciated

 

[pedro-egoli]

Further to above I have been searching for an answer and came across this code which has Paste Special /value into next column (supposedly!!!)

Sub Copy()

Sheets("accountbalance").Range("d2:d3").Copy
Sheets("Daily balances").Range("BO5:BO6").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
        Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.0"
    Selection.NumberFormat = "$#,##0"


End Sub

My worksheet is currently filled at column BN and when I ran code it pasted details into column "RJ5:RJ6" .
If I ran it again it moved to cells RK5:RK6 so it works but not to what I need.
How do I twig the code so that when I run it the first fime it fills cells BO5:BO6?
Thanks

 

[pedro-egoli]

Problem solved .
Realised I had the formula still in cells and once deleted the code works as I want
Thanks to anyone who looked to help here