Brand New LET Function in Excel!

In this video we're going to learn about the brand new LET function available in Office 365 insiders. 👇

Check out my full courses and ebooks here

DOWNLOAD the example workbook here

SUBSCRIBE & get my 3 FREE eBooks

CONNECT with me on social

Thanks for all your support!

[HowToExcelBlog]

Check out my full courses and ebooks here

[JimFikes]

I just heard about LET today, too. This is another great game changer for Excel. I love your example.

[jazzista]

John: Thanks for posting this introduction to the LET function. I just used it inside the filter function with Boolean math where I am substituting the ISBLANK function and it worked like a charm. This is the formula: =LET(ZeroValue, ISBLANK($H$18), FILTER(C22:F33, (ZeroValue+($D$22:$D$33=H18))*(ZeroValue+($E$22:$E$33=$J$18))))
I fell like It works kind of the VAR and RETURN in powerBI.

[wayneedmondson]

Hi John.. very cool! I just heard about this new function today. In the past, I've accomplished this kind of thing using Name Manager to define a name for a formula construct that will be nested and repeated in another formula. Once defined, I can reference the defined Name where needed vs. repeating the formula. But, Name Manager is awkward for this purpose. Having the capability built into a worksheet function is much more efficient.. can think of many good uses for it. Thanks for sharing and looking forward to more examples as you develop them. Thumbs up for How To Excel!!

[darrylmorgan]

Excellent Tutorial!Really Looking Forward To Trying The LET FUNCTION Looks Really Interesting..Thank You John :)

[stevereed]

Great explanation John, thank you. I'm getting to grips with the LET function and starting to really like it. However I created a unique list of some items and store in variable and then tried to use the variable in a MakeArray function that also uses Textsplit to have the text over 3 columns and it does not give the right result. If I replace the variable with the spilled dynamic array (#), it works as it should. Any ideas? Data, MAKEARRAY(ROWS(Flex_ul), 3, LAMBDA(rw, col, INDEX(TEXTSPLIT(INDEX(Flex_ul, rw, 0), " "), 1, col))) - where Flex_ul is the unique list.

[Soulus]

OMG. That is going to make spreadsheet maintenance and auditing so much easier!

[andrewmoss]

This will definitely shorten our formulas, particularly since the introduction of dynamic arrays — where we are writing longer formulas in fewer cells. The CHOOSE function is great for this purpose as you showed.

[Excelambda]

Amazing, incredible the power of creativity that comes with CHOOSE and dyn arrays. Anyhow, I manage to build a formula without CHOOSE, using TEXT formating in a IF statement.
=LET(
dt, B2,
fom, EOMONTH(dt, -1)+1,
eom, EOMONTH(dt, 0),
m, MONTH(fom),
wday, WEEKDAY(fom, 2),
arry, SEQUENCE(7, 7, fom-wday-6),
IF(MONTH(arry)<>m, IF(arry>(fom-wday), "", TEXT(arry, "ddd")), TEXT(arry, "d"))
)

[officesuperhero]

So this is basically a way to identify a variable. Correct?