I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".

I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").
I am struggling to do this with VBA...requesting the VBA guru's to help solve this.

On Sheet2
ID Data1 Data2 Data3 Data4 Data5 Data6 Data7
1 INDEX(Sheet1!D4:M16,MATCH(Sheet2!C3,Sheet1!D4:D16,0),2)
1 12
1 0
1 A
A
A

SHEET 1 (Dynamically increasing range both for columns and rows from starting Column "D")
ID Data1 Data2 Data3 Data4 Data5 Data6 Data7 Data8 Data9
1 12 1 2
1 12
1
1 A
A
A 12


1

A
4