Search, Data Input and Create Hyperlink ( The Complex Thought)

Hi.

I have worksheets with lots of data and various related certificates in the drive location. The fact is I want to create a hyperlink. Now, how the hyperlink create?

The File Location folder is D:\Certificate\ where the file name is like 5. T-234363.12_02_2022 in a specific file format ( Sl. Ref. Date)

The cell range is 10 to 1200.

Now in my worksheet in cell AD10 if I input 12/02/2022 then the system look at the file location for the "*.*.12_02_2022" file. If found then it creates a hyperlink to this cell and the Ref number (T-234363) will be input on the Cell AC10 As T-234363.

If any suggestion or expert opnion in this regard will be highly appreciated. Can it be done by vba? Hope someone kind-hearted will help me in this regard.

Thanks in Advance

Thank You S O

I am attaching the file (First Sample File.xlsm) for better understanding.

Thank you again.