Forum Discussion

Manish4993's avatar
Manish4993
Copper Contributor
Apr 25, 2021
Solved

Need help on Lambda #VALUE error

Hi Experts,   Please help me to catch the error with below LAMBDA function:   =LAMBDA(DOP,Sqdop,SqEoo,Model,SqModel,Insname,Sqinsname,OfferName,IFS(AND(DOP>=Sqdop,DOP<=SqEoo,Model=SqModel,Insname...
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 26, 2021

    Manish4993 

    I also have no idea why do we need recursion here and what to iterate with it.

    That's not an answer on direct question, but simplest way to return the result without copying the formula is to use structured table with simple formula like

    =IFNA(INDEX(Sales[Offer Name], XMATCH(1,
   ([@[Date of purchase]]>=Sales[Adjusted Start Date])*
   ([@[Date of purchase]]<=Sales[Adjusted End Date])*
   ([@Model]=Sales[Model])*
   ([Insuranace name]=Sales[Insuranace Type]))), "no such" )
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 26, 2021

    Manish4993 Not sure what you mean, but am wondering why you are using LAMBDA in the first place. Your goal is not very complicated and can be achieved by using FILTER. I've transformed your data to structured tables, so that the filtering becomes easier to follow. The end result is in column G.

     

    If you are merely doing this to learn LAMBDA formulae, someone else has to jump in and answer the part on recursion.

Resources