Can LET or LAMBDA help?

[kra808]

I have thousands of calculations like this..

=IF(M$2>=$C4,IF($D3=1,IF(ISNA(MATCH(M$2,Sheet6!$AE$5:$AE$29,0)),L6,XLOOKUP(M$2,Sheet6!$AE$5:$AE$29,Sheet6!$AF$5:$AF$29)*M5),IF($D3=2,IF(ISNA(MATCH(M$2,Sheet6!$AE$32:$AE$44,0)),L6,XLOOKUP(M$2,Sheet6!$AE$32:$AE$44,Sheet6!$AF$32:$AF$44)*M5),IF(ISNA(MATCH(M$2,Sheet6!$AE$47:$AE$59,0)),L6,XLOOKUP(M$2,Sheet6!$AE$47:$AE$59,Sheet6!$AF$47:$AF$59)*M5))),0).

This formula is checking logic based on many variables and is returning and calculating variables based on results of the logic. I can't seem to wrap my head around using LET or LAMBDA to help with simplicity and performance. Would using LET or LAMDA help a formula like this?

Thanks

 

[jasonb75]

Why not simply remove the unnecessary functions?
As far as I can see, this should work the same, it just takes half of the effort when a match is found.

=IF(M$2>=$C4,IF($D3=1,IFERROR(XLOOKUP(M$2,Sheet6!$AE$5:$AE$29,Sheet6!$AF$5:$AF$29)*M5,L6),IF($D3=2,IFERROR(XLOOKUP(M$2,Sheet6!$AE$32:$AE$44,Sheet6!$AF$32:$AF$44)*M5,L6),IFERROR(XLOOKUP(M$2,Sheet6!$AE$47:$AE$59,Sheet6!$AF$47:$AF$59)*M5,L6))),0)

 

[kra808]

Thanks jasonb, that works great. As you can probably tell, my Excel skills need a lot of work. Thanks again!

 

[jasonb75]

Everybody has to start somewhere

For information, LET is only useful if you are repeating the exact same calculation multiple times in the same cell.

I haven't received the LAMBDA update yet but from what I've read, I would say that there are too many variables with multiple lookups to make it practical.

 

[kra808]

Thanks for the additional comments on LET and LAMBDA. I was hoping that either one would be able to handle logic with variables, but I could see how.