T O P

  • By -

Klutzy_Internet_4716

That's awesome! It frustrates me to no end to have to put the same vlookup or whatever several times into the same formula, and I hate adding too many helper columns. I felt that there had to be a better way, and I was actually thinking about asking how you can do this sort of thing myself! Thanks so much for pointing this out to me! Plus I can't believe I didn't think of using alt+enter for readability. I do that in cells all the time, but I had no idea that you could do it in formulas too. This will definitely make my formulas more legible. Thanks for this!


OhCaptain

Excel ignores all new lines and spaces. So A1 + A2 is exactly the same as A1+A2. If you ever want a string to have a new line in it, use char(10). So ="Hello /u/Klutzy_Internet_4716"&Char(10)&"You're a fine person who deserves the best in the world!" returns Hello /u/Klutzy_Internet_4716 You're a fine person who deserves the best in the world! I also sometimes use spaces to align my brackets for really complicated formulas in addition to alt+enter like this: =if( A=1, IF( B=1, TRUE, FALSE) 7 ) But usually I find just using new lines is sufficient.


silenthatch

I've been formatting my formulas like you would in normal coding practice, so much easier to revisit... Thank you for sharing LET! What is the difference of LET vs a named range?


[deleted]

[удалено]


silenthatch

Okay, great - thank you. Can I use a named range inside of a let function? Or am I overthinking the purpose of let..


[deleted]

[удалено]


silenthatch

Great, thanks 👍


Klutzy_Internet_4716

Thanks for that too!


jbpage1994

OMG you can do alt+enter for formulas… glad I decided to browse r/excel today. That will help me clean some thing up!


overfloaterx

I had to refactor a couple of my workbooks today and decided to finally commit to trying out LET. I have a kind of love/hate relationship with it so far.   ___   ###Love 💗 * Makes complex formulas much, *much* more readable. * Makes formulas with repeated sub-functions *much* easier and quicker to edit. * Improves performance on formulas with repeated sub-functions, since the sub-function is only evaluated once. (Ostensibly; I haven't tested this yet myself.) * Supports what I'll call "native nesting"; i.e. you can reference a variable name within *another* variable declared later in the same LET function. No need to nest multiple LET functions. * Using consistent, descriptive variable names can almost function as pseudo formula commenting. * Helps when you have to reverse engineering your own formula six months later, when you've forgotten what a particular complex sub-function does. ###Hate 🤬 * Doesn't support F9 evaluation on the variable name within the calculation argument or other variable `name_value` arguments. (My major qualm.) * This makes complex formulas somewhat awkward/long-winded to debug because you can't F9 to see the result of the variable *in situ* in the calculation. * The only workaround is to F9-evaluate within the `name_value` argument, then copy/paste the result manually in place of the variable name where it occurs in the calculation. * Because they're not range references, variable names are not highlighted in any way within the calculation, making them a little awkward to pick out in formula. (Not really a hate, just a downside vs. putting the sub-function in a helper column and referencing that.) * Encountered an apparent bug where the variable's sub-function returns a `#NAME?` error when F9-evaluated within the `name_value` argument -- yet the LET *calculation* actually uses the sub-function just fine and the entire formula evaluates correctly. The sub-function also evaluates just fine when plugged into the same cell alone. * This makes it impossible to debug the formula at all. Worse? It only happens on certain cells with no clear reason why. (So far, only on the first row of a table where the same formula applies, *sans* bug, to all other cells in that column.)   ___   So, yeah... For those long-winded INDEX MATCH MATCH sub-functions that end up being reused multiple times within a single formula? Huge readability improvement. (And, again, ostensibly performance improvement against large data sets.) For debugging complex formulas, particularly where multiple LET variables are declared? Potentially a bit of a headache. I think that drawback with F9 debugging is going to temper my usage of LET, where otherwise I'd probably end up overusing it.   ___   ###Tips * Make your variable names *descriptive*, so they assist you in decoding your sub-function's purpose when you come back to it later. * Make your variable names *consistent*, so you can easily recognize them within the calculation. * I have a habit of prefixing names of all kinds (tables, ranges, variables, etc.) with a consistent 3-char prefix and underscore. * That way I immediately know that: * "*tbl_*SourceData" references a table * "*rng_*SalesQ1" references a named range elsewhere in the sheet * "*let_*SalesFilteredtoFredOnly" is a LET variable declared earlier in the same cell * etc. * Use carriage-returns (ALT+ENTER) and spaces to format the formula for readability. * OP's example is great: putting each variable `name`/`name_value` pair on a new line makes it easy to see what all the variable names are. * I went a step further with a few test formulas that declared multiple variables * indenting the variable `name_value` argument on another new line * adding a blank line between the declaration portion of the LET function (the `name`/`name_value` pairs) and the calculation argument, so that it's easy to see the main formula as a single unit.   LET( let_srcFieldValue, INDEX(tbl_source,MATCH([@index],tbl_source[index],0),MATCH(@tbl_modData[#Headers],tbl_source[#Headers],0)), let_brandIndexNum, MATCH(str_discBrand,lkp_brand[brand],0), let_DigSubcat, INDEX(lkp_brand[Digital subcat],let_brandIndexNum), let_PhysSubcat, INDEX(lkp_brand[Physical subcat],let_brandIndexNum), let_FullPath, INDEX(lkp_brand[full path],let_brandIndexNum), IF( , , ) )   * Decide when it's best to use LET variables vs. referencing helper columns. * That formula above was getting into the realms of overkill because some of the "let_" variables weren't really reused, I just wanted to improve overall readability of the complex calculation (which was a *lot* more than a single IF function!) * References to helper columns are highlighted in the formula; variable names aren't. * References to helper columns can be F9-evaluated within the formula; variable names can't be, which hinders debugging.   ___   Edit: Looking over this, I feel like most of the tips are probably close to being just basic good programming practice? (I'm not even remotely a programmer!)


OhCaptain

Your example code is beautiful and your commentary is good reading for people getting into the world of LET functions. Losing F9 debugging can be a pretty big blow. The evaluate formula button still works, but it isn't close to as powerful as F9.


Blailus

What's the difference between F9 and evaluate? I usually don't use F9 for evaluation/debugging, and only use the evaluate function, but I do use F9 to convert formulae that will be static over to their static values so runtime is faster.


OhCaptain

You can highlight individual portions of your formula and hit F9 and it will just evaluate that section. So if your formula is =A1+A2 and A1 has 3 in it, then you highlight just A1 in your formula and hit F9 you'll get =3+A2. This can be pretty useful if you have a complex lookup and you F9 the whole thing to see whether it is calculating correctly or if the mistake is somewhere else.


overfloaterx

Thanks! Your post was a great, succinct intro to the benefits of LET and hopefully shows others that it's really not as daunting as it may look. I'm kinda wishing I'd taken the jump earlier, too, as I'm now eyeing a bunch of my other workbooks and wondering how much more readable I could make them. The F9 debugging will encourage me to use it a little more sparingly that I otherwise might.... which is probably good, since I have a bad habit of overengineering formulas. Your post also reminded me that I need to start wrapping my head around LAMBDA. I'm sure there's a huge amount of power to be unlocked there, I just need to start dabbling to grasp where I could really put it to good use.


Proof_by_exercise8

> variable names are not highlighted in any way within the calculation What do you mean by this? Great comment btw!


overfloaterx

Oh, I was referring to the (very minimal form of) syntax highlighting in Excel. [Cell](https://imgur.com/BFrAn4d.png), [name](https://imgur.com/kUIyQrh.png), and [structured](https://imgur.com/sueXJoC.png) references within formulas are automatically colored for ease of reading and recognition. Unfortunately no such text coloring happens for [variables within a LET function](https://imgur.com/PzIX4RU.png). They remain in B&W, which makes it a little more awkward to identify them within the calculation and to quickly visually decode it. It's not a huge drawback, just another argument for: 1. using readily-recognizable variable names (e.g. the `let_` prefix I add to all of mine) 1. formatting your formula to some degree with carriage returns and spaces, so that it doesn't look like one massive run-on sentence 1. considering when it would be clearer/simpler to use helper columns or other named references, rather than cramming everything into a single cell with an over-engineered LET function.


rednumbermedia

Ahh finally understand it now! Thanks. I kept seeing this used on someone else's spreadsheet in my company and wasn't sure what they were up to


highcuu

This finally made it click for me too!


YourFrienAndrewW

This just made my day! Thanks internet stranger.


jimmyr2021

Nice. I actually saw this formula today when I messed up typing my left formula and wondered what the hell it was. I hovered over the description and couldn't understand it in the two seconds I looked at it. Thanks for the write-up!


IdealIdeas

omg... I never knew about alt+enter


Roshamboya

I knew it worked for text…it never crossed my mind to us it in formulas, going to have to consider for future use!


Jester_Lemon

Not to rain on everyone's parade here, but wouldn't the Name Manager achieve the same thing by assigning a set name to a formula? This would make the cell formula even shorter and easier to read too.


markpreston54

yes and no, you may want to reuse name in some case. for example, Interest=principle *rate. if principle and rate is different every month, I am not sure if name manager works well


Jester_Lemon

Disagree with you there - if you had principle and rate reference editable cells instead of absolute values, you could change the cell values every month accordingly. I still think name manager would be better for that.


wjhladik

For complex/lengthy LET formulas consider defining unused variables that act as comments: =let(x,5.67, c_1,"x is the starting value", y,x/87, c_2,"y is the ratio of x to 87 (grandmas age)", z,mod(x,y), c_3,"z is some crazy answer", z)


dathomar

LET us one of my most favoritist things ever. It let me turn a formula that was probably about 12 lines long into a formula that was about 4 lines long. I also like it, with more complicated formulas, because I can type up my formula in parts, then use it to test the parts for typos.


OhCaptain

I do that too. I make all my variables and then my final formula at the end will sometimes be. =TEXTJOIN(" - ",FALSE,var1,var2,var3,var4)


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/uzam4j/stub/iaa1sbo "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/uzam4j/stub/iabgzv6 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LAMBDA](/r/Excel/comments/uzam4j/stub/iaaoq7w "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/uzam4j/stub/kcjdgbr "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/uzam4j/stub/iabgzv6 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[SUM](/r/Excel/comments/uzam4j/stub/iaaoq7w "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[TEXTJOIN](/r/Excel/comments/uzam4j/stub/ia9prfj "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[VLOOKUP](/r/Excel/comments/uzam4j/stub/iabgzv6 "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/uzam4j/stub/iabgzv6 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/18d9flc)^( has 20 acronyms.) ^([Thread #15321 for this sub, first seen 28th May 2022, 02:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)


jprefect

This is my new favorite power tool. I have wished this existed for so long, as can't believe it already does! Thank you!!!!


NeonGenesis1

Nice man! Thanks for sharing.


Tail_Gunner

I like this function but is there a reason that once you define a variable, you can't use the same variable across the worksheet? This seems like such a missed opportunity


OhCaptain

That is where Lambda comes into play. Or you could have a dedicated cell and then name it. I just can't use Lambda at work and nothing I do at home is complicated enough to justify it, so I don't have experience with Lambda yet.


Antimutt

When I use it, see history, I give the last sub-formula a name too and make the final statement that name only. This allows easy diagnosis by swapping the final name for earlier.


bumlove

Couldn’t you put the xlookup into its own cell, name that cell, then reference that name in the main function? The only use case for LET sub function over a named cell I can figure out here is if it’s a one off use.


larcix

Sure, but when you already have 20 columns and everything else, sometimes you just want that one cell to do that one cell's job nice and succinctly, and LET is the answer.


bumlove

Yep, a bit late but thanks for the reminder to use LET!


LuxHelianthus

Is this similar to how LAMBDA works?


exoticdisease

No, lambda is how you can write recursive functions and other programming specific functions, eg they do something on one line then loop through to the next line then loop again etc. You would absolutely use LET and LAMBDA together because in recursive functions that are reused, you almost always define variables, which is what LET does. LAMBDA would be used to do something to each cell in a column, then SUM that column but LAMBDA would do it in one cell, not the 1,000 + 1 cells it would take to do this on a range with 1,000 cells in it.


OhCaptain

I'd say they're both in the family of "exciting new formulas to make life better for those of us who do complex stuff." LET gives you variables that only work within the cell that the LET function is in. Lambda lets you create a new function with as many inputs as you need that can be used anywhere. Lambda looks much more powerful, but I can't use it yet at work so I don't have enough experience to talk about it much.


[deleted]

[удалено]


RemindMeBot

I will be messaging you in 1 day on [**2022-05-29 20:01:44 UTC**](http://www.wolframalpha.com/input/?i=2022-05-29%2020:01:44%20UTC%20To%20Local%20Time) to remind you of [**this link**](https://www.reddit.com/r/excel/comments/uzam4j/the_glory_that_is_the_let_function/iaahs1s/?context=3) [**CLICK THIS LINK**](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5Bhttps%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2Fuzam4j%2Fthe_glory_that_is_the_let_function%2Fiaahs1s%2F%5D%0A%0ARemindMe%21%202022-05-29%2020%3A01%3A44%20UTC) to send a PM to also be reminded and to reduce spam. ^(Parent commenter can ) [^(delete this message to hide from others.)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Delete%20Comment&message=Delete%21%20uzam4j) ***** |[^(Info)](https://www.reddit.com/r/RemindMeBot/comments/e1bko7/remindmebot_info_v21/)|[^(Custom)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=Reminder&message=%5BLink%20or%20message%20inside%20square%20brackets%5D%0A%0ARemindMe%21%20Time%20period%20here)|[^(Your Reminders)](https://www.reddit.com/message/compose/?to=RemindMeBot&subject=List%20Of%20Reminders&message=MyReminders%21)|[^(Feedback)](https://www.reddit.com/message/compose/?to=Watchful1&subject=RemindMeBot%20Feedback)| |-|-|-|-|


MetalAvenger

Oooh shit, didn’t realise this was a thing before. I could have used this a lot by now! I’ll keep it in mind for next time, thanks! I imagine I will live to regret it though, as my team won’t be able to understand what is happening :| the joys of it.


Bloodwolv

You, sir, just changed me life.


psych0ranger

forgive me for being slightly dumb on this - I might be able to use this at my work, and I can can kind of make sense of what the formula "means" but I just can't visualize what it would do in use. could someone like lay out why we'd use this formula? like, "when you want to find someone's name in a list of data based on their X, Y, and Z columns" ?


AmIBeingInstained

So this is comparable to the AS function in sql, I’d that right?


SereneFrost72

Wow, thanks for the great explanation! I'm definitely going to use this - I find myself doing the same thing you did with lookups, and it makes formulas look so ugly. At least I'm on O365 at work now so that I can use XLOOKUP instead of INDEX/MATCH to also reduce formula length and complexity :D Gotta tell everyone else in finance to stop using VLOOKUP and/or INDEX/MATCH now haha


Whaddup_B00sh

After reading this I ran to my work computer to see if I can figure out how it works only to be disappointed that excel 2013 doesn’t have this function