Below is a possible solution to the challenge – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)?
Enter the below formula in B1 as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.
=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(1,–(MMULT(–((Sheet2!A1:J20)=A1),
TRANSPOSE(COLUMN(A1:J20)^0))>0),0)+ROW(A1)-1,MATCH(A1,OFFSET(Sheet2!A1,
MATCH(1,–(MMULT(–(A1=Sheet2!A1:J20),TRANSPOSE((COLUMN(A1:J20)^0)))>0),0)
-1,0,1,COLUMNS(A1:J20)),0)+COLUMN(A1)-1),"Take me to the Word – "&A1)
The workbook illustrating the above solution can be downloaded from Solution – Challenge 44 – Hyperlink to a cell in another worksheet by specific text (not cell number)