Investigating the LET function
The LET function is used to simplify formulas. It is a method to get Excel to compute the same expression used within a formula by naming it, leading to more efficient processing, and making the formula easier to understand as it is less complex in construction.
Let’s look at the breakdown of the LET function structure: =LET (name1, value1, [name2/value2], …, result).
Constructing LET (syntax)
The following table lists the syntax and explanation for each argument within the LET
function:
Table 10.4 – LET syntax explanation
We will now look at a formula example that consists of using the same expression more than once within it. The dataset in A2:J21 contains a list of customers and related
training information. If we wanted to extract customer information to another part of the worksheet and return a ? (question mark) for every blank cell in the extracted range, we could use the IF, ISBLANK, and FILTER functions.
Now, let’s work practically through it:
- Open the workbook named LET.xlsx.
- On the first worksheet, we will start the formula by typing the = sign followed by the IF( function. As we are including cells that could contain blank cells in the extracted range (and would like to include a ? in the empty cells after extraction), the ISBLANK function will need to become part of the formula. Type ISBLANK( followed by the FILTER( function.
- Next, we will include the FILTER array and the criteria. The array is A2:J21, but will display as TR in this workbook as it is defined as a named range. Type TR,TR=”David”)),.
- To finish off the ISBLANK part of the formula, enter “?”,.
- To conclude the formula, type FILTER(TR,TR[Name]=”David”)).
-
To recap, the formula should now read as follows:
=IF(ISBLANK(FILTER(TR,TR[Name]=”David”))
,”?”,FILTER(TR,TR[Name]=”David”)).
- Press Enter to display the dynamic array comprising a list of only David Sueu’s training courses, filling any blank cells with a ?.
- Here is the result of the formula construction:
Figure 10.25 – The result of the IF, ISBLANK, and FILTER combinations
- This is a long-winded function to construct, where functions are used multiple times. So, we can be a little more efficient in constructing this using the LET function.
-
Update the formula using the following LET formula construction:
=LET(mylist,”David”,myrange,FILTER(A2:J21, A2:A21=mylist),IF(ISBLANK(myrange),”?”,myrange)).
-
To explain the formula, let’s break up the steps. After starting the formula using
=LET(, define the first variable name and the value for the variable, in this case, David, =LET(mylist,”David”,.
- Now, define the second variable name. This is the filtered range A2:J21 in the case of the example =LET(mylist,”David”,myrange,.
- Lastly, update the formula using the variable names where applicable: FILTER(A2:J21,A2:A21=mylist),IF(ISBLANK(myrange), “?”,myrange)).
- Press Enter to see the result of the LET function.
The next topic will concentrate on a set of database functions that are extremely useful and efficient.
