Detailed LET function example

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.

Concatenation

This formula uses concatenation, which means joining values to form text strings. For example, with the number 100 is in cell A1, we can use concatenation to write a formula like this:

="You have "&A1&" points."

which returns this text string:

"You have 100 points"

Note: Excel has several functions for concatenation including CONCAT and TEXTJOIN, but I generally prefer to use the ampersand (&) operator for this kind of problem.

The lookups

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:

=VLOOKUP(G5,B5:D16,2,0) // look up name
=VLOOKUP(G5,B5:D16,3,0) // look up points

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:

="Hi, "&VLOOKUP(G5,B5:D16,2,0)&", you have "&VLOOKUP(G5,B5:D16,3,0)&" points."

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:

=IF(VLOOKUP(G5,B5:D16,3,0)>300," Great job, "&VLOOKUP(G5,B5:D16,2,0)&"!","")

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(VLOOKUP(G5,B5:D16,3,0)>300 // check points

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:

="Hi, "&VLOOKUP(G5,B5:D16,2,0)&", you have "&VLOOKUP(G5,B5:D16,3,0)&" points."&IF(VLOOKUP(G5,B5:D16,3,0)>300," Great job, "&VLOOKUP(G5,B5:D16,2,0)&"!","")

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.

Implementing LET

The basic pattern for implementing LET with two variables looks like this:

=LET(name1,value1,name2,value2,result)

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:

=VLOOKUP(G5,B5:D16,2,0) // look up name
=VLOOKUP(G5,B5:D16,3,0) // look up points

Putting this into the LET function, we begin like this:

=LET(name,VLOOKUP(G5,B5:D16,2,0),points,VLOOKUP(G5,B5:D16,3,0),

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:

="Hi, "&VLOOKUP(G5,B5:D16,2,0)&", you have "&VLOOKUP(G5,B5:D16,3,0)&" points."&IF(VLOOKUP(G5,B5:D16,3,0)>300," Great job, "&VLOOKUP(G5,B5:D16,2,0)&"!","")

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:

=LET(name,VLOOKUP(G5,B5:D16,2,0),points,VLOOKUP(G5,B5:D16,3,0),"Hi, "&name&", you have "&points&" points."&IF(points>300," Great job, "&name&"!",""))

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:

=LET(
name,VLOOKUP(G5,B5:D16,2,0),
points,VLOOKUP(G5,B5:D16,3,0),
"Hi, "&name&", you have "&points&" points."&IF(points>300," Great job, "&name&"!",""))

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.

More readability

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:

=LET(
name,VLOOKUP(G5,B5:D16,2,0),
points,VLOOKUP(G5,B5:D16,3,0),
part1,"Hi, "&name&", you have "&points&" points.",
part2,IF(points>300," Great job, "&name&"!",""),
part1&part2)

This alternative won't run any faster, but it can make the formula easier to read and create.

With XLOOKUP

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:

=LET(
name,XLOOKUP(G5,B5:B16,C5:C16),
points,XLOOKUP(G5,B5:B16,D5:D16),
part1,"Hi, "&name&", you have "&points&" points.",
part2,IF(points>300," Great job, "&name&"!",""),
part1&part2)

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: