Hyperlink to External Workbook with Cell Reference

I have two separate workbooks (ie SOURCE & ACTIVE), both utilizing excel online, that I'm trying to perform a combination Hyperlink with a nested cell reference inside.

As this cell formula will be used hundreds/thousands of times within a sheet, the formula needs to be able to automatically leverage/reference cell addresses as it's copied down. There may be easier ways to perform this with VBA or Office Scripts, but our org has disabled access to those for us.

In the ACTIVE workbook, within SheetX in cell A4 I'd like a hyperlink that will take the user to the first row in column A, within the SOURCE workbook, in this case Sheet2, that matches the contents of cell B4 from the ACTIVE workbook, and have the "friendly name" of the hyperlink to be the row number of that first occurrence of the matching string.

I have two formulae that are as close as I'm able to get to the solution at the moment.

The first formula returns the exact result I need, but because it's a hardcoded link to the exact cell, I can't leverage changing cell locations for the hyperlink itself:

=HYPERLINK("https://COMPANY.sharepoint.com/:x:/r/personal/USERNAME/Documents
/SOURCE%20FILE%20NAME.xlsx?d=w***web=***=D***&nav=M***0",
MATCH(B12,'https://COMPANY.sharepoint.com/personal/USERNAME/Documents
/[SOURCEFILENAME.xlsx]Sheet2'!$A:$A,0))

The second formula is the best I can approximate what the correct final formula should be, but it doesn't work. I think my issue may be with the formatting of the URL itself. I know there are intricacies about where and when to place double quotes (") tick marks (`) and other special characters, but I don't understand those exact rules for it.

=HYPERLINK("https://COMPANY.sharepoint.com/personal/USERNAME/Documents
/[SOURCEFILENAME.xlsx]Sheet2!"&ADDRESS(
MATCH(B4,https://COMPANY.sharepoint.com/personal/USERNAME/Documents
/[SOURCEFILENAME.xlsx]Sheet2!$A$4:$A$89,0),1),
MATCH(B4,'https://COMPANY.sharepoint.com/personal/USERNAME/
Documents/[SOURCEFILENAME.xlsx]Sheet2'!$A:$A,0))

I found this old thread that helped get me a bit closer, but can't get it to work with an external workbook. And all other threads I've found are just for offline versions of excel.

Am I on the right path for this? And just to reiterate, this is for excel online workbooks.