# LET function in EXCEL 365

Microsoft Excel 365 gives us a new function – LET(). It is not available for every Microsoft 365 subscriber though. A few weeks ago my Excel did not have this function, but now I can work with it. I did a few examples when I was learning this feature and want to share them with you today.

# What is LET function?

Microsoft’s definition:

The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.

LET function description

Few more details about the parameters:

Name1 – a name of the variable/expression; has to start with a letter and cannot be the output of a formula

Name_value1..127 – a value/formula for the corresponding name

Calculation_or_name2..127 – this one has two modes: the first serves as a place for calculations using names defined before, and the second acts as Name1, i.e. defines a name for another variable in relation to Name_value_2..127

# Example 1 – single name

Here is the simplest example of the LET function application. We need to provide at least three parameters:

Name1 – the name starting with a letter of a single variable/expression; cannot use formulas here `myName`

Name_value1 – an actual value or an expression `CONCAT("M","a","g","d","a")`

Calculation_or_name2 – can be a formula that uses the Name1 `"My name is: " & myName`

As a result we will get a string `My name is: Magda`

# Example 2 – multiple names

The definition of the LET function allows us to provide more (up to 126) defined pairs (name:value), so let’s see how we can add one more.

Name1 – the name starting with a letter of a single variable/expression; cannot use formulas here `myName`

Name_value1 – an actual value or an expression `CONCAT("M","a","g","d","a")`

Calculation_or_name2 – this one changes now, as it becomes a name of a second variable, so all the rules apply `liveIn`

Name_value2 – an actual value or an expression of the second name `"Southampton"`

Calculation_or_name3 – now the final calculation is that uses both names the Name1 and Name2 `"My name is: " & myName & " and I live in: “&liveIn`

As a result, we will get a string `My name is: Magda and I live in: Southampton`

# Example 3 – reusing values

As my last example I want to see the parameters being reused in multiple calculations

Name1 – the name starting with a letter of a single variable/expression; cannot use formulas here `myName`

Name_value1 – an actual value or an expression `CONCAT("M","a","g","d","a")`

Calculation_or_name2 – second variable `text`

Name_value2 – as a value using formula with Name1 `SUBSTITUTE(myName&" was here"," ","-")`

Calculation_or_name3 – in the final calculation I am using second variable text multiple times `UPPER(text)&" | "&LOWER(text)&" | "&PROPER(text)`

As a result, we will get a string `MAGDA-WAS-HERE | magda-was-here | Magda-Was-Here`

So far I did not find why it is throwing an error #NAME?, but it is gone when I remove the first variable.

Thank you,

Magda

Tags:

Categories:

Updated: