=HYPERLINK("#" & ADDRESS(MATCH(Planner!$F$4,Planner!$A$11:$A$164,0)+10,1,,,"Planner"), IFERROR(INDEX(Planner!$B$11:$B$164,SMALL(IF((Planner!$F$4=Planner!$A$11:$A$330),ROW(Planner!$A$11:$A$308)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),""))
Perhaps I greatly misunderstand, but nothing I see in the question or comments seems to contradict the first line in the question that says the sheet is in the same spreadsheet. So paths and filenames seem unnecessary.
So, instead of thinking of generating the value and then hyperlinking it, think in terms of generating an address for the hyperlink function to act upon achieving the hyperlinking action, and use HYPERLINK
's second parameter, that of a "friendly name" to display in the cell for the formula you give that generates the value.
So your value will display in the cell, but clicking on it will activate the hyperlink to go to the generated address.
Speaking of which, I suppose there is some reason for the curious way you generate the lookup. I suppose that because you use a variety of ranges in the formula that differ from each other in that they use different, and different size, sets of rows where a lookup like XLOOKUP
would have the ranges rather more in lockstep. I guess. Not asking. But because the given formula is essentially a simple lookup, for the demonstration of the solution, I used a simple MATCH
of F4 on the column A range to generate the row number (the result plus the 10 rows not included before the range starts) and a simple 1 for the column in the ADDRESS
function. This will actually work for your situation no matter the reason for your unique lookup mechanism, as the address you want is the simple MATCH
I did anyway.
The only complication in the ADDRESS
function is that the place you want the hyperlink to take you is on sheet Planner
so you must specify that as ADDRESS
's final parameter.
To recap, HYPERLINK
will use the ADDRESS
and MATCH
mechanism to create the address to link to. It will also generate a "friendly name" for display in the cell that just happens to be the value your lookup finds. So you display the value you wish to display, but have a working hyperlink to take you to the source.
NOTE: Sadly, you cannot place a similar mechanism in the cells you go to thinking to then click those hyperlinks to return to the places you started. Excel will see it as an infinite loop and quit, giving you an error. But you can use this technique in a cell next to this one's target (and therefore easy to click after looking at the target) which can return you. The fact that the cell right next to this one's target may have nothing at all to do with the situation doesn't matter. It is conveniently close to the user's visual attention center (the target of this hyperlink) so no aggravation for him to click the cell next to it to return to his starting place.
index()
in there somewhere.="#Planner!B" & SMALL(IF($F$4=Planner!$A$11:$A$330,ROW(Planner!$A$11:$A$308)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS(Sheet1!$A$1:A1))+10
#Planner!B13
[file name]
in front of the#
symbol. Make sure to use the brackets.