Error with Excel Lambda function in Name manager

Question

I have set up a Lambda formula that calculates interpolated values from a given set of coordinates

• similar to existing trend/linest/forecast functions
• handles non consecutive inputs as well as errors (searched value outside of range, etc.)

=LAMBDA(searchvalue,searchrange,valuerange,
   LET(
     LB,XLOOKUP(searchvalue,searchrange,searchrange,,-1),
     UB,XLOOKUP(searchvalue,searchrange,searchrange,,1),
     LV,XLOOKUP(searchvalue,searchrange,valuerange,,-1),
     UV,XLOOKUP(searchvalue,searchrange,valuerange,,1),
     IF(searchvalue<=MIN(searchrange),UV,
       IF(searchvalue>MAX(searchrange),LV,
         IF(LB=UB,average(LV,UV),
           LV+(searchvalue-LB)/(UB-LB)*(UV-LV)
         )
       )
     )
))

(credit to https://www.stellingconsulting.nl/MS_Excel_LAMBDA.php for giving me a headstart)

I have successfully tested it and used it in my worksheets with values and cell references. Proceeded to put it in Name manager as L_InterpolateX and use it as a custom function: fed it with my searchvalue, searchrange and searchrange inputs which worked just fine as of last night.

I picked up my file today and now all my formulas return #VALUE. Copying-pasting the Lambda formula from name manager into the worksheet still works fine.

Calculating sheet/file/full recalculation won't solve the issue. Saving file won't solve the issue. Rebooting file sometimes seems to help but rather erratically (and obviously not a solution by any means). Going into Name manager, editing the Name and pressing OK (no change needed) seems to solve the issue.

Has anybody else experienced this odd behavior? And is there any setup I am missing as to how the named function can work on a consistent, reliable basis?

(for now I have added a VBA line to automate Name editing of Lambda functions in a Workbook Open event but the point was to stand clear of macros...)

Answer 1

This is a known bug since the most recent Excel update (the one that brought GROUPBY and PIVOTBY off the experimental branch). It's been reported across the internet so hopefully Microsoft is aware and will fix