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!
Marcus_Booth sorry I didn't see this response until just now for whatever reason. I actually can't even navigate to it when viewing the full discussion, as the "show more" action doesn't appear underneath the parent comment. The only reason I found it was because I got a notification that someone "liked" the parent comment. Still a lot of bugs with the new community platform, but I digress.
Simply change the first argument of REDUCE from EXPλ("Field"&_fId,2) to EXPλ("") and the "Field x" row will be gone. Cheers!
IMHO, if change profile settings on
it'll be bit easier to navigate in thread.
- djclementsSep 27, 2025Silver Contributor
Thanks Sergei! That's exactly what I wanted. :)