What would be a perfect formula to get Display of Merged Cell Reference ONLY of Value in One sheet of Particular Col which is matched in another

[RapchikM]

Hello

What would be a perfect formula to get Display of Merged Cell Reference ONLY of Value in One sheet of Particular Col which is matched in another sheet with addtional text or rather the Matched or Looked up value is contained with other text ?

I tried seprataing but the structure got completey haywired

Any chances with your utmost help and Mastered knowledge will I be able to derive using a perfect Formula in Sheet4

Am sorry i forgot to use XL2BB and Please Excuse me

Sheet 1 Structure

Col D

Row 5 494667020303913
Row 6 693667020303913
and So On........
Blank rows are also there
Row 31 58633702030913
Row 32 23020766372913


Sheet 2 Structure

Col D Merged with Col E with additional text Int:XXX and Tax:XXX

Row 15 494667020303913 Int:837.00 and TAX:83.00
Row 16 693667020303913 Int:837.00 and TAX:83.00
So on.......
Row 48 58633702030913 Int:3350.00 and TAX:335.00
Row 49 23020766372913 Int:3350.00 and TAX:335.00


Sheet 3 Structure

Col D Merged with Col E with additional text Int:XXX and Tax:XXX

Row 15 58633702030913 Int:3350.00 and TAX:335.00
Row 16 23020766372913 Int:3350.00 and TAX:335.00
So on.......
Row 25 494667020303913 Int:837.00 and TAX:83.00
Row 26 693667020303913 Int:837.00 and TAX:83.00

Sheet 4 Result Deisred
Col A Col B Col C Col D
Row 4 Sr.No In Sheet 2 Cell Ref In Sheet 3 Cell Ref
Row 5 1 494667020303913 D15 D25
Row 6 2 693667020303913 D16 D26
Blank Rows Copied too as per Sheet1
Row 31 3 58633702030913 D48 D15
Row 32 4 23020766372913 D49 D16
So on

RapchikM

 

[RapchikM]

Sheet 4 Result

Sr. No		In Sheet 2 Cell Ref	In sheet 3 Cell Ref
1​	494667020303913	D15	D25
2​	693667020303913	D16	D26
3​	58633702030913	D48	D15
4​	23020766372913	D49	D16

While Posting #1 i made error of Rows and Serial No. Kindly excuse

RapchikM

 

[RapchikM]

Hello Guys
After Much exploration i tried the following method

=CELL("address",INDEX(Sheet2!$D$15:$D$174,MATCH($B2&"*",Sheet2!$D$15:$D$174,0)))

The Result that i get is
'[Trial.xlsx]Sheet2'!$D$15 in Cell C2 instead of D15 only
How to Remove the File Name, Sheet 2, and $ representation

RapchikM