Forum Discussion
Additional help needed with existing formula using LAMDA- Excel 365
- Aug 24, 2025
I reorganized the sample file, so each table now contains the same number of columns, in the same order, as the updated SheetMap. The only thing I had to change about the formula (from rev3.0) was the _cId variable to set the field mapping:
=LET( _rep, RESULTS!$A$2, _beg, RESULTS!$B$2, _end, RESULTS!$C$2, _lst, RESULTS!$A$5:$A$12, _inc, (RESULTS!$E$5:$E$12="EXCEED")+(RESULTS!$E$5:$E$12="NOT MET"), IF( NOT(OR(_inc)), "[no records found]", LET( VALλ, LAMBDA(lbl,tId,csv,LAMBDA(x,CHOOSE(x,lbl,tId,csv))), EXPλ, LAMBDA(array,[rws],EXPAND(array,rws,9,"")), INCλ, LAMBDA(field3,field2,(field3=_rep)*(field2>=_beg)*(field2<=_end)), _fId, SEQUENCE(,9), _cId, VSTACK("1,2,3,4,,,5,13,14", "1,2,3,4,,6,5,10,11", "1,2,3,4,,,5,11,12", "1,2,3,4,6,,,8,9", "1,2,3,4,,,5,11,12", "1,2,3,4,7,6,5,12,13", "1,2,3,4,6,,5,14,15", "1,2,3,4,6,7,8,14,19"), REDUCE( EXPλ("Field"&_fId,2), FILTER(MAP(_lst,SEQUENCE(ROWS(_lst)),_cId,VALλ),_inc), LAMBDA(acc,val, LET( tbl, INDIRECT("tbl"&SUBSTITUTE(SUBSTITUTE(val(1)," ",),"-",)&"[#ALL]"), arr, SORTBY(EXPλ(CHOOSECOLS(tbl,--TEXTSPLIT(val(3),",",,1))),SORTBY(_fId,TEXTSPLIT(val(3),",")="")), bdy, DROP(arr,1), qry, FILTER(bdy,INCλ(CHOOSECOLS(bdy,3),CHOOSECOLS(bdy,2))), VSTACK(acc,EXPλ("["&val(2)&"] "&val(1)&":"),EXPλ(TAKE(arr,1)),EXPλ(IF(ISBLANK(qry),"",qry),ROWS(qry)+1)) ) ) ) ) ) )
I also added a 'FieldMap' worksheet to the attached revision, so you can see how the field mapping was generated (there are 4 formulas highlighted in blue, as well as a note showing the formula used as the Data Validation List Source for range C5:U12).
If you're still having issues applying these methods to your master workbook, I'm afraid there's not much more I can do to help aside from gaining access to your actual workbook to troubleshoot the problem.
Best of luck!
It didn't work. Just returns #VALUE! in the cell. Sorry, but I'm not familiar with what you are using. I can't really point to anything in the formula. Thanks for the try. :)
The problem is in column E (GOAL) on the RESULTS worksheet in your 082625 stripped-down file. It's not visibly obvious because the results are hidden from view (via conditional formatting) when column D="N/A". If you remove the formatting, you'll see the formulas in column E are returning #VALUE! errors when column D="N/A". Your original 061625 sample file had a different formula in this column that worked properly. Either restore the original formula, or try the following in cell E5 (and copy down):
=IFS(D5="n/a","",D5<F5,"NOT MET",D5>G5,"EXCEED",1,"MET")
Once this is corrected, you should be able to successfully implement ANY of the methods I've suggested thus far. If you need help tweaking anything else thereafter, just let me know.
- Marcus_BoothAug 28, 2025Iron Contributor
That fixed the issue! Thank you very much for sticking with me on this. Much appreciated!! I'm wondering if it would be possible to not show the 'Field x' that appears above the columns. No big deal if it's not an easy fix. We can certainly deal with it as is. Thanks again!!