When a different ID is typed into cell G5, the VLOOKUP function retrieves a new name and points from the data in B5:D16. Note: the LET function is new in Excel 365. It’s part of the new dynamic array functions in Excel. If points are over 300, the message is extended: 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: which returns this text string: 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: 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: which evaluates to a final result of: We now have the first part of the message completed. The second part is conditional. If points are greater than 300, then we add: 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: When the id is 131, VLOOKUP returns “Finn” and we get the following text string: 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.
Implementing LET
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: 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.
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: 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: 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:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
