so in this video let's talk about lambda function in excel
so lambda function is a function that returns another function so let's do an example of that so
with this function you're going to provide one or more parameters so you can name those parameters
whatever you like like function arguments so i could do a and you could keep doing as many as
you need so let's say right now we need just two so once you provide your list of arguments
then you follow it as the last argument to be the calculation you're going to do using those
arguments so to do an example of that we could just take that a and let's multiply it by b for
now let's just keep it simple so right now we have this lambda function that will accept two
parameters and then it will do this calculation it will multiply those parameters so how do
you use this so for right now i'm just gonna hit enter now think about a function in excel product
for example if i had three here and four here i can go with this product function
and say take this comma this close parenthesis so i'm going to provide those two arguments
3 and 4 to this product function and when i hit enter i'm going to get my answer 12. so now we're
gonna do that same thing here using this lambda function that i've created so think about this as
this the function name product without parentheses and this part so if i wanted to
call that product function then what i do i open parentheses and provide those two
arguments so the same thing we're going to do here if i go here i can open parentheses
and then i can do this argument comma this argument close parentheses and if i hit enter see
how i'm going to get the same 12. so essentially what's happening here this b5 is going to be a
b6 is going to be b and then this is our calculation now if you wanted this
function to accept let's say three arguments then you would do a b c and then we can do
some sort of calculation whatever we're going to do here and then finally now because we have three
arguments in this function we need to provide that as a parameter so we'll just do a comma
and for now i'm gonna link to b7 which is currently empty which is gonna be like zero so
the answer is zero but if i add a number here see now we have these three numbers multiplied
together using this lambda function so we'll get to actually useful cases on this but before we get
to it let's actually talk about how we simplify this so we don't have to write this entire crazy
formula so what if i wanted to just instead of typing this say multiply 3 or something like that
so what i could do i could store this function without the actual function call in a name manager
so i can copy this i'm going to hit enter for now i'll go to our formulas and here i'll open
name manager and here we can store different names now you may be familiar with this when you store
a named range it's the same box so what i do here i click new and paste
my formula right here that lambda formula and then here i provide the name for this
so i'm going to call this multiply 3 like this so you can call it pretty much anything you want
just make sure there are no special characters in this and then also that it doesn't interfere with
existing functions so don't name it like sum because sum already exists so at this
point if i press ok because i have this named formula i should be able to just simply go to
here and instead of doing all of this i can simply just replace this with that name which is multiply
3 like this and hit enter and see i got the same answer so now we should be able to use that across
the worksheet anywhere else right so i can go here and do multiply three open parenthesis and again
the same thing i could take this times this maybe times this this time close parenthesis hit enter
and as you can see it works so now we end up basically creating an internal function inside of
the spreadsheet that we can use so now let's see why this would even be useful so let's do a couple
of cases here so the reason you'll use this is to simplify certain formulas so sometimes you'll have
formulas that just are too busy and difficult to understand and you could just make a version of it
that's a lot easier to work with let's say we want something that's going to get us the first word
out of some phrase like here so the way we would do this for example i could use search function
and i would do find a space comma and i would find that space here in this text so this is going to
give me the position of that space which is eight now if i wanted to get the word machine out of
this then i want to get this one two three four five six seven characters not including the space
which is the character number eight so what i would do i would use left function to do this so i
would do left and then i would point to this text and then comma and then say this minus 1 because
we want one less character to not include space so if i enter that's going to give me a machine
and here instead of using the cell b1 as a reference now i can go back here grab this
search function cut it out of this and go back and just paste it right here instead of that b1
and now we got one formula that does this however this formula needs more handling so for example
if i drag this formula down see it works for a lot of these but it doesn't work for this one
because this one doesn't have that space it's just a single word so i need to handle that in
this case i probably just want to return that word as is so i'm going to go back to my formula here
a simple way to handle this we could just say if error so if this formula gets an error then let's
just return whatever's already in the cell like this and this way if i drag this down see if it's
a single word then it's going to return that word we could also handle this in a different
way we could have said if we search and there is no space then let's return the word something
like that but for now to keep it simple i'm gonna add one more handling just for these empty cases
so what i'll do i'll just add if this cell equals blank comma then let's just return blank comma in
all the other cases we're going to just use this if error so now it should be a lot cleaner
let's delete this our formula works it's not really about this formula in particular but
now we have a little more complicated formula that keeps referring to this a1
cell like bunch of times now how nice would this be if we could just say get first word
open parenthesis and then just click on this close parenthesis and hit enter right now
obviously it's not going to work right now because there's no function called
get first word so what we do we create a lambda function so i'm going to go ahead and
copy this without the equal sign hit escape and here i'm going to create a lambda function
here what i'll do i'll provide a parameter i'm just going to say phrase as parameter name comma
i'm only going to need one parameter because if you remember we kept referring to the same a1 cell
so what i'll do here as the calculation after that parameter i'll just paste that entire formula
and then i'll close parentheses for this whole thing and that would be my lambda
function except that instead of saying a1 in this function i will simply just use this parameter
phrase in all those cases so i'll do phrase for this phrase for this phrase for this
and phrase for this and now this is a lambda function meaning that i could just open
parentheses after this again to call the function point to the cell close parenthesis and hit enter
and that should return exactly the same thing and i could just drag it down
and it works the same way and now because it's a lambda function i can just grab this function
without the execution part copied hit escape go to my name manager and basically just create a
new name paste the function here and call it get first word just like that press ok close this
so now i should be able to just go here and do get first word open parenthesis click on this
close parenthesis hit enter and i got my results without having all that complicated
stuff in my formula and just like that i got this so hopefully now you can see the power of this
now let's do another example let's say i want another function that's going to be
get last row and basically what i want to do i want to be able to just pass a column like this
and when i hit enter it should tell me what's the last row in this column now i want to make
sure that gets me the actual last row meaning that if i have like empty cells in the middle
here like this that should still give me nine so again one way we could do this we could say
x match and as a lookup value i can basically do something like star question star in codes
comma then i can point to this whole column a comma then i can do this wildcard character match
which is two comma and then finally i'm gonna do search last to first like this close parenthesis
and hit enter and as you can see i got 9 which is the row 9. so if you want to understand
exactly how this function works i have a separate video covering this here i have this function
that does what it does now i can grab this and convert it to a lambda function so how do we
do that again i'm going to copy that function hit escape and then i'm going to do a lambda function
and here we're going to need a parameter the only parameter we had
was this column so i'll just call it column or range
do a comma and then i'm going to paste my formula right here close parentheses on this
and then i'm going to replace this column with my actual parameter i'm going to copy that and paste
it here let's just go this way a little bit and finally at this point i'm not going to call the
function anymore here i'll just go straight to creating this so i'll just copy this whole thing
hit enter that's fine and go to my name manager on top here click new i'm going to call this get
last row and i'm just going to paste my formula right here press ok so now i should have a new
function i can just go here and do equals get last row open parenthesis refer to a column
like this close parenthesis hit enter and i got my 9 without having all of this stuff so now we
have a nice short function that should allow us to get the last row if i wanted to also use it again
maybe for this column that's all i have to do to make it happen
and then if i have something different it should see find whichever one the last row is
and as you can see this gives me seven this gives me nine
and that should do it for this video thanks for watching and i'll see in the next video