Forum Discussion

Kendethar's avatar
Kendethar
Iron Contributor
Aug 14, 2022
Solved

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

Hello all,   I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function.   I have a complex formula in ran...
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • lori_m's avatar
    lori_m
    Aug 15, 2022

    After further investigation, the basic problem with extending the formula given in the post to a dynamic array seems to be that HYPERLINK doesn't accept arrays in both arguments simultaneously.

     

    Instead maybe try making a single HYPERLINK formula that uses relative referencing,

    =IF(I3:I1000="","",HYPERLINK(
LET(x,ISNUMBER(SEARCH("Bank",@I3:I1000)),
    y,VALUE(LEFT(@I3:I1000,FIND(" ",@I3:I1000)-1)),
    z,IF(x,'Tracker Import'!$D$2:$D$1000,'Bank Import'!$F$2:$F$1000),
    IF(x,"#'Tracker Import'!","#'Bank Import'!")&ADDRESS(MATCH(y,z,0)+1,IF(x,4,6))),
IF(ISNUMBER(SEARCH("Bank",I3:I1000)),"View Log","View Transaction")))

    (note this may require clicking "No" in the compatibility checker after entry)
    Another possibility that avoids the use of @ could be:

    =IF(I3:I1000="","",HYPERLINK(
"#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(ISNUMBER(SEARCH("Bank",I3:I1000)),"View Log","View Transaction")))
Kendethar's avatar
Kendethar
Iron Contributor
Mar 13, 2025

Updated for when result should be looked for and found in another column:

=IFERROR(FILTER(IF($I$3:$I$1000="","",HYPERLINK(
IF($I3="<No Mismatches>","",
"#LET(x,ISNUMBER(SEARCH(""Bank"",rc9)),
      y,VALUE(LEFT(rc9,FIND("" "",rc9)-1)),
      z,IF(x,'Tracker Import'!r2c4:r1000c4,'Bank Import'!r2c17:r1000c17),
      n,'Tracker Import'!r2c5:r1000c5,
      XLOOKUP(y,z,z,XLOOKUP(""*""&ABS(y)&""*"",n,n,,2,1)))"),
IF($I3="<No Mismatches>","",
IF(ISNUMBER(SEARCH("Bank",$I$3:$I$1000)),"View Log","View Transaction")))),$I$3:$I$1000<>""),"")

 

Resources