SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array

lori_m  these options are fascinating to me even though I can't get them to work.  I tried multiple variations of using the '@' symbol but don't seem to have the right combination to make it work in my case.  As for the second option I'm fascinated with the use of the #LET inside the quotes.  Is HYPERLINK 'evaluating' that expresssion?  Again I wasn't able to get it to work in my case but again really interesting and would love some more insight into what you are doing here.

I did get HYPERLINK to output an array of links with 'friendly' names.  I posted the solution in an earlier thread where you and other were toiling with the use of INDEX( .... , XMATCH( array, array) ) as my solution was a variation on that technique.  ( hyperlink-function-do-not-support-dynamic-arrays ).  Basically I added the friendly name as a second column to the array that the INDEX returns then used TAKE to only return that friendly name column.

lori_m 

This is genius, thank you! Works perfectly. I'm not too familiar with the =LET() function yet with how new it is. So, working on that skill still but I can see it has many applications. 

A few small things:

1st, if the user clicks a null hyperlink outside of the listed "View Transaction/Log"s, Excel returns a "Reference isn't valid." error, which was an OCD thing for me. Therefore, I added a =FILTER() function to narrow down the spill, per the mismatch range that's not empty.

2nd, I forgot to mention that my mismatch range will display <No Mismatches> in range $I$3, when that is true.

If this is true, range $K$3 will return "View Transaction" and if the user clicks it, Excel returns the "Reference isn't valid." error. So I added an =IF() function to the link_location and [friendly_name] arguments to check if $I$3 has that text.

3rd, I changed the relative references to absolute, still works fine. 

4th, I didn't integrate an =IFERROR() function like I usually do for sheet cleanliness since everything is essentially covered. 

Formula (spill from range $K$3 - tweak of lori_m's):

=FILTER(
IF($I$3:$I$1000="","",HYPERLINK(
IF($I$3="<No Mismatches>","",
"#LET(x,ISNUMBER(SEARCH(""Bank"",rc9)),
      y,VALUE(LEFT(rc9,FIND("" "",rc9)-1)),
      z,IF(x,'Tracker Import'!r2c4:r1000c4,'Bank Import'!r2c6:r1000c6),
      XLOOKUP(y,z,z))"),
IF($I$3="<No Mismatches>","",
IF(ISNUMBER(SEARCH("Bank",$I$3:$I$1000)),"View Log","View Transaction")))),$I$3:$I$1000<>"")

Thanks again and keep up the awesome work!