How to add a hyperlink to a outputted value to link it its source

Question

I have the following formula which outputs a value from another sheet. This is functioning just as it should.

=IFERROR(INDEX(Planner!$B$11:$B$164,SMALL(IF(($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))),"")

The formula and functions above are new to me, so I am not entirely sure how to add a link.

The formula above is looping through the data I have to check if the row has $F$4 in it, so adding the hyperlink is a bit more complex.

How can I add a hyperlink to the output, so that it will hyperlink to where the source cell?

Main sheet

Planner Sheet

Answer 1

Interesting question, and getting to the answer requires understanding a few principals:

1. The formula you implemented is known as "n-th lookup". Normal Excel VLOOKUP will return only the first match, but with "n-th lookup" you create a list of all matching entries (with each list entry containing its row index number if it matches, or "" if it doesn't), then use the SMALL function to return the n'th smallest matching row index number. n in this case is derived from the current row number the formula is in, so that as you copy and paste the "n-th lookup" formula downwards, you are successively returning the next matching row index. Finally the formula uses INDEX to look up the associated value from column B at that index.
2. INDEX returns the value as a reference, meaning it can also be used as an argument in other functions that require a references such as CELL
3. HYPERLINK can generate a link to a cell in the current open Excel file, but requires a text reference to the cell and not just a normal reference. This is because HYPERLINK is primarily intended to link to websites or documents via a text URL. Luckily we can use the CELL function to return a text URL or address to any reference in the current workbook, e.g. =HYPERLINK(CELL("address",Sheet1!A1),"Go to cell A1")

We now have all the ingredients to mix together a working formula:

=HYPERLINK(CELL("address",__INDEX PART OF YOUR FORMULA__),__YOUR WHOLE FORMULA__)

So

=HYPERLINK(CELL("address", INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1)))), IFERROR(INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),"")

We have to remove the IFERROR part, as IFERROR forces the reference returned by INDEX to resolve to its value, meaning CELL would not be able to resolve it. Unfortunately as a consequence hyperlink will return a #NUM! error when their is no longer any matching rows to return. To fix this, we simply have to replace the "" returned by the ELSE part of the IF with a the last index number in the list i.e. 373-11+1

=HYPERLINK(CELL("address", INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,373-11+1),ROWS($A$1:A1)))), IFERROR(INDEX(Planner!$B$11:$B$373,SMALL(IF(($F$4=Planner!$A$11:$A$373),ROW(Planner!$A$11:$A$373)-MIN(ROW(Planner!$B$11:$B$373))+1,""),ROWS($A$1:A1))),"")

Answer 2

You can use the following, using exactly the formula segments you currently use.

It will display the lookup result, but the hyperlink will go to the located value's actual cell.

HYPERLINK("#Planner!B"  &  TargetRow,  Target)

For Target, substitute your entire current formula. This is the part that will show the located value in your output cell/s.

For TargetRow you only need a portion of the current formula, that portion that generates the row which INDEX returns.

Since you know the sheet, you just enter it as text, and that would feel logical. But you also know the column you desire in the row found: column B. While it might not feel logical to simply enter that as text when contemplating how to do the hyperlink, once it occurs to you, it's natural to do so.

So put together, it looks like so: (until you can move users forward and use LET)

= HYPERLINK("#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,  IFERROR(INDEX(Planner!$B$11:$B$164, 
SMALL(IF(($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))),""))

A potential formatting quirk comes to mind. My version is 2205 and when I click anywhere in the cell, it activates the hyperlink effect. In every version I've had prior to this (allowing for not making a new HYPERLINK formula for perhaps six months), the full cell clicking ability only happened if I chose a non-standard horizontal alignment option when formatting the cell. Otherwise, it only happened if I clicked directly on the displayed contents and NOT in whitespace. Something to consider if your users report "um, the hyperlink doesn't work" and that applies to any hyperlink, not just these. Conversely, if you experience the "clickable whitespace selects the cell, not the hyperlink" effect, that too can be of use in other applications.

(Interestingly, when I use LET (not available in Excel-2010 which you have tagged the question with), the formula result does not get formatted as a hyperlink. So if you eventually can use that function to make the formula look simple, you would have to manually format the formula's cells to look like a hyperlink (underlined, blue color) though a clicked cell's hyperlink would not change to the purple color showing it had been used at least once.)