Using LET Function inside HYPERLINK Function

[SanjayGMusafir]

Hi Experts
I was using following formula, which was working fine.

=HYPERLINK("#"&
IFERROR((IFS(DAY(TODAY())<=2,
ADDRESS(MATCH(1,INDEX((Banks[Dt]>=EOMONTH(TODAY(),-1))*(Banks[Acc]="CC ICICI SGM")*(Banks[Particulars]="Payment"),0,1),0)+5,COLUMN(Banks[Dt]),4,1),
TRUE,
ADDRESS(MATCH(1,INDEX((Banks[Dt]>=EOMONTH(TODAY(),0))*(Banks[Acc]="CC ICICI SGM")*(Banks[Particulars]="Payment"),0,1),0)+5,COLUMN(Banks[Dt]),4,1))),""),
"aSGM (02/20)"
&
LET(BNm,"CC ICICI SGM",LUNow,XLOOKUP(BNm,CCLimit[CCard],CCLimit[uNow]),LUTY,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bThisYr]),
         IFS(LUNow<>"",LUNow,LUTY>0,"`",TRUE,"")))

Then I thought of making it shorter using LET function as below, and it stopped working. When I convert LET Function to Value using F9 key it works. But it isn't working as it is...

=HYPERLINK("#"&
LET(BNm,"CC ICICI SGM",BDt,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bDt]),mDt,IFS(DAY(TODAY())<=BDt,EOMONTH(TODAY(),-1),TRUE,EOMONTH(TODAY(),0)),
          ADDRESS(MATCH(1,INDEX((Banks[Dt]>=mDt)*(Banks[Acc]=BNm)*(Banks[Particulars]="Payment"),0,1),0)+5,COLUMN(Banks[Dt]),4,1)),
          "iSGM (02/20)"
&LET(BNm,"CC ICICI SGM",LUNow,XLOOKUP(BNm,CCLimit[CCard],CCLimit[uNow]),LUTY,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bThisYr]),
         IFS(LUNow<>"",LUNow,LUTY>0,"`",TRUE,"")))

I must be missing something very important that I'm unable to identify.

Please help
Thanks in Advance

 

[SanjayGMusafir]

I think I worked it out as below. Yet any better suggestions are always welcome, as there is always a scope for improvement.
Thanks

=LET(BNm,"CC ICICI SGM",
BDt,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bDt]),
mDt,IFS(DAY(TODAY())<=BDt,EOMONTH(TODAY(),-1),TRUE,EOMONTH(TODAY(),0)),
LUNow,XLOOKUP(BNm,CCLimit[CCard],CCLimit[uNow]),
LUTY,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bThisYr]),
HYPERLINK("#"&
ADDRESS(MATCH(1,INDEX((Banks[Dt]>=mDt)*(Banks[Acc]=BNm)*(Banks[Particulars]="Payment"),0,1),0)+5,COLUMN(Banks[Dt]),4,1),
"iSGM (02/20)")
&
IFS(LUNow<>"",LUNow,LUTY>0,"`",TRUE,""))

 

[SanjayGMusafir]

Just in case anyone reaches this thread this is the actual solution. The formula in #2 and this one have very refined differences but effect functionality.

=LET(BNm,"CC SBI SGM",
          BDt,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bDt]),
          mDt,IFS(AND(BDt>12,DAY(TODAY())>BDt),EOMONTH(TODAY(),1),AND(BDt<=12,DAY(TODAY())<=BDt),EOMONTH(TODAY(),-1),TRUE,EOMONTH(TODAY(),0)),
          LUNow,XLOOKUP(BNm,CCLimit[CCard],CCLimit[uNow]),
          LUTY,XLOOKUP(BNm,CCLimit[CCard],CCLimit[bThisYr]),
HYPERLINK("#"&
          ADDRESS(MATCH(1,INDEX((Banks[Dt]>=mDt)*(Banks[Acc]=BNm)*(Banks[Particulars]="Payment"),0,1),0)+5,COLUMN(Banks[Dt]),4,1),
          "sSGM (24/13)"
&
IFS(LUNow<>"",LUNow,LUTY>0,"`",TRUE,"")))