Need help on Lambda #VALUE error

[Manish4993]

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=Sqinsname),OfferName))

After passing the correct parameters it is still show the #value error

[#2]

@Manish4993 Difficult to diagnose this without seeing the file and the formula at work. But the MS support page for LAMBDA says this:

So, are you really passing all (and not too many) arguments? And I assume that you have named your function something like "CheckDOP" in the Name Manger and that the Lambda function expression is entered there. And then, when you call the function you enter something like =CheckDOP(..........) with all eight parameters between the brackets.

If all of this makes no sense at all, perhaps you can upload you workbook. Remove any private or confidential information, though.

[#3]

@Riny_van_Eekelen 

Hi Riny,

Please find the attachment for better understanding of problem.

[#4]

@Manish4993 

You call with has 9 parameters lambda which has only 8 ones. If you have no recursion it's always better to test formula on simple LET and after that wrap by LAMBDA

=LET(
 DOP,       Data!B2,
 Sqdop,     'Sales Qty'!E2,
 SqEnd,     Data!B2,
 Model,     'Sales Qty'!F2,
 Sqmodel,   Data!A2,
 INSname,   'Sales Qty'!D2,
 SqInsname, Data!E2,
 Offername, 'Sales Qty'!C2,
 IFS(AND(DOP>=Sqdop,DOP<=SqEnd,Model=Sqmodel,INSname=SqInsname),Offername,
     TRUE, 'Sales Qty'!A2)
)

[#5]

Both the data set ranges will go long and i will also use the recursion to make it dynamic. So i don't know the actual error that need to be solved as pet attached file.

[#6]

@Manish4993 

Actual error is different number of parameters in function and it's call.

All the rest is more about how to avoid such errors, one of main techniques is in-cell editing. With some tricks that could be used for the function with recursion. But that's in theory, you concrete function doesn't have recursion.

[#7]

If possible please share the correct formula file with me by using the same file that earlier i shared with you.

[#8 · Accepted solution]

@Manish4993 Just as I suspected and @Sergei Baklan pointed out, you have one argument too many. See attached.

[#9]

@Manish4993 

It depends on which logic is to be for the last parameter. For example

=LAMBDA(DOP,Sqdop,SqEnd,Model,Sqmodel,INSname,SqInsname,Offername,Othername,
   IFS( AND(
         DOP>=Sqdop,
         DOP<=SqEnd,
         Model=Sqmodel,
         INSname=SqInsname
       ),Offername,
      TRUE, Othername))(Data!B2,
                       'Sales Qty'!E2,
                        Data!B2,
                       'Sales Qty'!F2,
                        Data!A2,
                       'Sales Qty'!D2,
                        Data!E2,
                       'Sales Qty'!C2,
                       'Sales Qty'!A2)

Function is G2. If logic is correct you may remove parameters and add to Name Manager.

[#10]

@Riny_van_Eekelen

i need one more help.

How to use recursion in same file. updated file is attached for reference.  

[#11]

@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.

[#12]

@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" )