There are many ways to find and retrieve data from a table or range based on a lookup value. In fact, because Microsoft often comes up with new, modernized alternatives, there are too many ways! So, ...

Prevent broken formulas using tables, validation rules, separate worksheets, named variables, and the power of LAMBDA.

Office Scripts extract Excel hyperlink URLs without macros; results are hardcoded so the file can stay .xlsx, reuse is straightforward.