Apr 25 2021 01:23 AM
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
Apr 25 2021 07:28 AM
@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.
Apr 25 2021 09:15 AM
Apr 25 2021 09:57 AM
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)
)
Apr 25 2021 10:05 AM
Apr 25 2021 10:20 AM
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.
Apr 25 2021 10:59 AM
Apr 25 2021 11:12 AM - edited Apr 25 2021 11:13 AM
Solution@Manish4993 Just as I suspected and @Sergei Baklan pointed out, you have one argument too many. See attached.
Apr 25 2021 11:24 AM
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.
Apr 26 2021 12:17 AM
i need one more help.
How to use recursion in same file. updated file is attached for reference.
Apr 26 2021 01:11 AM
@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.
Apr 26 2021 01:57 AM
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" )
Apr 25 2021 11:12 AM - edited Apr 25 2021 11:13 AM
Solution@Manish4993 Just as I suspected and @Sergei Baklan pointed out, you have one argument too many. See attached.