is there a way to make a dynamic hyperlink to matching text in excel? I have a spreadsheet that has company names and one is companies we've already got history with. so for the ones we already interact with I'd like to create a hyperlink to the same company on a different page within the same spreadsheet. I would just hyperlink to cell but sometimes the text changes place as it's an ever changing document.

looking for something like (using apple as company example)

hyperlink ("companies sheet [cell containing "apple" text]", "partners sheet [cell containing "apple" text])

does this exist?

CodePudding user response：

Have you looked at the cell, match and index functions? MATCH: https://www.exceldemy.com/excel-match-function/ INDEX: https://www.exceldemy.com/index-function-excel/ CELL: https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

The MATCH function returns the position of a lookup value in a range.

INDEX returns the value at a given location in a range and CELL returns information about a cell in a worksheet.

example: =HYPERLINK("#"&CELL("address",INDEX(Details!B5:B9,MATCH(D4,Details!B5:B9,0))),"Click to See Details")

The MATCH function returns the position when it finds the match and then INDEX traverses to that cell to retrieve the value. But here the CELL function plays its part. You can see we have set “address” as the info_type of CELL. So, rather than fetching the value, it will get the cell reference itself.