Using Let or Lambda
I’m used to building models where inputs are explicitly shown in worksheets. I like the idea of using Let and Lambda, I just worry that it w
The idea behind LET and LAMBDA is to make complex things simpler. If your solution is already simple, you probably don't need to add them. There are some relatively simple use cases though.
Let's say you are performing an XLOOKUP, but you want return a default value if the result of the lookup is blank. This happens a lot when you are pulling in columns from another data source. A record with the matching ID is found, but a particular attribute is blank, so you want to use another column from the current data set instead.
=IF(XLOOKUP(A1, Data!A2:A100, Data!B2:B100, "Not found")<>"", XLOOKUP(A1, Data!A2:A100, Data!B2:B100, "Not found"), B1)
That formula works fine, but the lookup is repeated twice. Not only is it long, but it doubles the computational effort required. LET allows us to assign the lookup to a variable, then reference that later in the calculation.
=LET( field, XLOOKUP(A1, Data!A2:A100, Data!B2:B100, "Not found"), IF( field<>"", field, B1 ) )
I added the line breaks and indentation to make this easier to read, but you could put this all on one line if you want. Using LET allows us to reference the field we looked up later.
But what about LAMBDA? Let's say you're doing this same operation over and over in your workbook. You could just copy/paste this formula down, but if the information in the Data sheet is something you frequently pull in, it might make sense to parameterize this into a named LAMBDA so you can call it easier anywhere in your workbook. Let's see what that might look like, and what we might want to parameterize.
The value in A1 is what we're looking up. So that definitely needs to be a function parameter. The default value in B1 is also something we'd want control over. So let's lay that out:
=LAMBDA(id, [default_value], LET( field, XLOOKUP(id, Data!A2:A100, Data!B2:B100, "Not found"), IF( field<>"", field, IF(ISOMITTED(default_value), "", default_value) ) ))
If we add this to Name Manager as LUFIELD, we can call it anywhere in our workbook like this: =LUFIELD(A1). Notice that I made the default_value parmeter optional. I could also use the function like this =LUFIELD(A1, B1).
This lightweight method of identifying your parameters and building custom functions is really useful. I have all sorts of custom LAMBDA functions I use in my models. Pretty much any business logic you routinely use can be parameterized and created as a function. Then you pass in columns of data as parameters and get a spilled range result of your output.
