I discovered named ranges only a couple of months ago, and they’re pretty amazing, as is the spilling behavior that fills an entire column from one cell. When it comes to the specifics of dealing with complex formulas, I usually copy paste them to a text editor because the formula bar doesn’t have a great editing ui.
How far did you get with let and lambda? It seems like you could build whole programs on them, but I haven’t pushed very hard on them yet. Do you want to say more about the “heavy technical limitations”?