Detailed LET function example
The LET function can simplify certain formulas by making it possible to define and reuse variables. In the example shown, the LET function is used to define two variables: name and points. The formula in G6 is:
When a different ID is typed into cell G5, the VLOOKUP function retrieves a new name and points from the data in B5:D16.
In this example, we have a simple set of data in B5:D16 that includes ID, Name, and Points. The goal is to generate a custom message for any name in the list by entering a valid ID in cell G5. The message uses the name from column C and the points in column D like this:
"Hi [name], you have [points] points."
If points are over 300, the message is extended:
"Hi [name], you have [points] points. Great job, [name]!"
In the article below, we'll build up the formula step-by-step, then streamline the final formula with the LET function.
="You have "&A1&" points."
which returns this text string:
"You have 100 points"
The initial task is to use a numeric ID to lookup the name and points. To keep things simple, we're going to use VLOOKUP like this:
With a valid ID in G5, VLOOKUP will retrieve a name and corresponding points. For more on VLOOKUP, see the detailed overview here. At the end of the article, we look at how to replace VLOOKUP with XLOOKUP.
Formula without LET
Next, we need to concatenate the name and points we looked up in the final message. The first part of the message looks like this:
So far, so good. If we type the number 131 into G5, we get:
="Hi, "&"Finn"&", you have "&"342"&" points!"
which evaluates to a final result of:
"Hi, Finn, you have 342 points!"
We now have the first part of the message completed. The second part is conditional. If points are greater than 300, then we add:
"Great job, [name]!"
If points are not greater than 300, we add nothing. We can do this with the IF function together with VLOOKUP like this:
Here again, we use VLOOKUP to fetch both points and name. Starting on the left, we first check if points are greater than 300:
If not, we just return an empty string (""). If points are greater than 300, we build part 2:
"Great job, "&VLOOKUP(G5,B5:D16,2,0)&"!"
When the id is 131, VLOOKUP returns "Finn" and we get the following text string:
"Great job, Finn!"
Finally, we need to join the first part of the message to the second part. The final formula is:
This formula works fine, but it's getting a bit unwieldy. Notice we have four separate calls to VLOOKUP, and two of the four are exact duplicates:
Let's use the LET function to slim down and simplify this formula.
Thinking about variables
In order to use the LET function, we need to think about variables. The main purpose of variables is to define a useful name that can be reused elsewhere in the formula code. In addition, when the value assigned to a variable is calculated, there is an opportunity to improve performance by reducing the number of times the calculation is performed. Using a variable also has the advantage of keeping a calculation in one place only, which reduces errors and the editing needed to keep multiple copies in sync.
Looking at the formula above, there are two obvious places where a named variable makes sense: the lookup for name, and the lookup for points. Both of these lookups appear twice in the formula, so this is a good opportunity to simplify.
The basic pattern for implementing LET with two variables looks like this:
Notice names and values appear in pairs – we declare name1 and assign value1, then we declare name2 and assign value2. Lastly, we add a result. The result is the final result returned by LET. This is typically a calculation, adjusted to use the declared variables.
In this case, we'll use "name" for name1, and "points" for name2. To assign values to each variable, we'll use the VLOOKUP function configured as shown below:
Putting this into the LET function, we begin like this:
This defines the variables name and points and assigns values to both, based on the id in cell G5. Next, we need to add the calculation that determines a final result. We'll start with the original formula above:
We could use this formula as-is, and it would run correctly, but it would defeat the purpose of using the LET function. To take advantage of LET, we need to replace the VLOOKUPs with the variables we've already declared, like this:
="Hi, "&name&", you have "&points&" points."&IF(points>300," Great job, "&name&"!","")
Now we need to add this code to the LET function as the last argument:
This formula will return the same result as the original formula, but notice we only use VLOOKUP twice, instead of four times, and the calculation part of the formula is somewhat easier to read, since we are using name and points as variables.
To improve readability, we can add line breaks (Alt + enter) like this:
You will see formatting like this frequently in formulas that use the LET function, because it makes the formula easier to read, write, and edit. Here is the result in Excel. I've added one additional line break between part 1 and part 2 to keep everything on screen:
Note: you'll need to expand the formula bar (control + shift + u) to see extra lines.
You are free to add more variables to LET if it helps you work with the formula more efficiently. For example, we could break the message into two parts, part1 and part2, then join them together at the end like this:
This alternative won't run any faster, but it can make the formula easier to read and create.
One nice thing about LET is that it isolates calculation steps in a way that makes them easier to change. For example, although we've been using VLOOKUP to retrieve the name and points values we need, we can easily swap out VLOOKUP for XLOOKUP like this:
Notice we only changed the way name and points are defined. The rest of the formula did not change. Here is what this formula looks like in Excel with the formula bar expanded: