Excel Lambda Function (Examples) - All You Need to Know!

hello and welcome to another Excel tips video I'm Sumit bunel and in today's video I will show you everything you need to know about the Lambda function now the Lambda function has been in Excel for a couple of years now and it is a different function it's not like other regular Excel functions it's a little different and it has a very specific use case but once you completely understand how it works it can be very powerful in some situations just to give you an idea you can use a Lambda function to create your own custom functions without knowing any VBA without any programming right within a cell you can write a Lambda function and then convert it into your own formula and you will see how powerful that is now before I get into the Lambda function and show you how to create that and how to use it I urge you to watch this video completely because when I start explaining the Lambda function with some simple examples you may think it's not impressive you may think it's worthless but it's not I will explain with a basic example and then I'm going to move to more advanced use case where you will start to see how Lambda function could be useful so let's get into the worksheet and see how it works here I have these two numbers in cell A2 and B2 and I want to add these two numbers now obviously you don't need a Lambda function for this you can do this using a simple formula such as A2 + B2 or you can use a sum function such as sum A2 and then comma B2 now this obviously is a very simple example but let me show you how we can use a Lambda and create our own function to just add these two numbers and once I've covered how the Lambda function works then we can move on to some more advanced examples so to create a Lambda function let me first explain the arguments it takes so the Lambda function can take a parameter or calculation so if I want to write a Lambda function let me write it here for you it would take parameters which could be something like a comma B and then the calculation which involves these parameters so which could be something like a + b now these two are are like any function arguments where let's say if we are using some function then we need to provide these two cell references A2 and B2 these are just like cell references or these could be numeric or text values and then in the end always in the end we would have the calculation that we want Lambda to do so what we want in this case is Lambda should take two arguments A and B and then it should give us the result which is A + B now this is the same construct that will be followed in all the examples but the idea here is you need to first give Lambda an argument which would be something like an a b or it could be any string and then finally you will give the calculation that would involve those strings those values those variables so let me create a Lambda function here and the Lambda function I would create here would be again let's keep it simple and let's call it a comma B and then a + b and in this case if I hit enter it is going to give me the calculation error because as of now I have created the Lambda fun would use this Lambda function in the back end so the way this works is you need to create a named range and put this Lambda function in the named range so that you can then use the name of that named range as a function so let me show you how it works I would hold the control key and press the F3 key so it opens the name manager you can also do the same thing if you go to the formulas tab here and then click on name manager now when this name manager opens click on the new Option here and here I'm going to create a new name now let me call this name add to number and you can call it anything you want and this is going to become my new function that I can use in the worksheet and in refers to I need to put that Lambda that I created which would be with an equal to sign so equal to Lambda a comma B and A+ B and now when I click okay my function has been created now I can go anywhere in my workbook and I would have this function add two numbers and you can see it is now showing up in intellisense if I double click on it it will also show me the number of arguments which it takes which is a and b obviously it doesn't show me anything more as to what A and B should be but it just tells me that it takes two arguments which means it uses two arguments in the formula and now I can come here I can say A2 comma B2 and it works fine now again as I said this is not impressive because I can do this either by just using A2 plus B2 or using a simple sum function but now you understand that how to create a Lambda function and then put it in the named range so that you can name your own function and use it in Excel so this is a very basic example let's move to something which is a little more complicated here I have these names their region and their sales value and I want to calculate the commission for them now to calculate the commission the condition is that if the sales value is more than 50,000 then the commission is going to be 5% and if the sales value is less than 50,000 then the commission is going to be 3% so I can create a simple if formula where I would check whether this cell value is more than 50,000 and if it is then the commission here is going to be C2 * 5% else it is going to be C2 multiplied by 3% and now when I hit enter it gives me this result here and I can drag it down for the entire column now let's create a Lambda function from this so what I'm going to do is first let me copy this formula here and let me show you how to approach creating a Lambda function so when you creating a Lambda function it is always first a good idea to create the function in the worksheet so that you know exactly what is the calculation you want to use then within this formula that I want to convert into a Lambda see how many arguments it needs from the user from me how many arguments does it need and in this case this formula needs one argument which is the cell reference that has the sales value which is C2 this means that when I'm creating a Lambda function this is going to be my my parameter this is going to be my only parameter because this is the only input it needs from the user so I need to create a parameter that would give it the sales value and then I can have the calculation which is going to be this formula so let me copy this formula and let's create that Lambda function so here I need to provide the parameter and as I said there is only one parameter that I need which needs to be the sales value let's call it sales and then I need to provide the calculation which is going to be this if formula here but I also need to rep Place C2 with this parameter so I would copy sales and I would instead of C2 I would put sales here so what I've done is with this very specific IF function I've created a Lambda out of it and now if I want to test this Lambda if I just hit enter here it is going to give me calculation error but if I want to test this lamb Lambda I need to provide the value let's provide this value here and now I can check whe whether my Lambda function is working or not so when I hit enter it gives me this result which is exactly the same as this one and I can drag it down so I know that my Lambda function is working now let's create my own function using this Lambda function so I'm going to copy this part here only the Lambda part here because I do not need this argument here so I'm going to remove this and I've copied my Lambda function now now I will hold the control key and press the F3 key so it opens the name manager and here I'm going to click on new and let me call this function sales Com or sales commission and in refers 2 I'm going to paste the the Lambda function that I created and now when I click okay it has created my Lambda function you can double check that the formula is right and now I can come here and I can use my Lambda function I will use the name that I gave my Lambda function which is sales com and you can see it tells me that it only takes one argument which is the sales argument so I can come here I can use this and now when I hit enter it gives me the result so you can see I have taken a formula which was although not very complicated to begin with and I have converted into a Lambda function which is so simple it just takes one single argument so if you are sharing this worksheet worksheet with someone else or you have to use this formula multiple times in your own workbook this is a lot easier to use as compared to this formula but let's see if we make it a little more complicated than what happens let's say in this case I also have one more criteria where if the region is us then the commission is going to be 5% no matter what but if the region is Not Us Then the commission is going to be 5% for more than 50,000 value and 3% for less than 50 ,000 value so now I will have to change this formula so let me copy this and let me create a new formula where I would first check whether this is equal to us or not and if it is equal to us then my S commission would automatically be 5% else I would have to then check the sales value which would be this if C2 is more than 50,000 then 5% else 3% so you can see now I have a formula which is slightly more complicated than the previous one because there are now more conditions so if if I want to convert this into a Lambda again the same approach you come here you put the formula here in another cell and see how many arguments it is taking from the user and it is taking two arguments B2 and C2 so I'm going to have a Lambda function which would have two parameters so let's create that Lambda function where there is going to be two parameter the first one is going to be region the second one is going to be sales and then the calculation which is going to be that if function and this is my Lambda function now I just need to replace B2 with the region and C2 with sales so I can quickly do this here and this becomes my Lambda function if I hit enter obviously I get a calculation error so I need to provide the values just to see that my Lambda function is working so this is my region value and this is my sales value and I can see that my Lambda function is working fine so I can copy this function again hold the control key press the F3 key to open this and let's just uh redo the sales commission function here itself so I'm going to remove the old Lambda function that I had and I would put the new one here and now make sure that I have an equal to sign here and now when I click okay it should work so let me remove this and let's say sales commission and when I put it here you can see now it takes two arguments the region argument and the sales argument the region argument is going to be this the sales argument is going to be this and now when I hit enter it gives me the same result now as you can see this is slightly longer complicated formula with two if functions as compared to a Lambda function which is just one single function which takes two arguments so this is a lot lot easier to use and as your formulas start getting complicated you would realize that converting them into Lambda function is going to be a lot more efficient for you now let me show you a couple more examples so here I have this text in column A and I want to know what is the word count in each cell so I can use a formula which is Len and I can first get the length of all uh these characters in this cell and because I'm not sure whether there could be an extra space leading or trailing I can use trim function and now if I hit enter it gives me the value 13 which is the total number of characters here then from this I'm going to subtract all the characters that are not space characters so in this case I'm going to first come here and I would use substitute function and within substitute let me use trim again and I would use this so within substitute function this is my text my old text that I don't want is a space character and I want to replace it with a null character or blank and this becomes my second Len function now if I hit enter it gives me two here because there are two space characters and because I want to get the total number of words I would have to add one to it so I can add one here and this is going to give me the total number of words in each of these cells now if I want to convert this into a Lambda function again very simple uh we'll follow the same method I followed in the last example so I'm going to copy this formula here now I would see how many arguments it requires from me which is just one single argument A2 which is the cell that has the text so I'm going to copy this here and I'm going to create a Lambda function again I'm hoping that now you are getting the drift of how the Lambda function can be created so I would just create this Lambda function let's call this text the the argument and then the function which is this entire function and now I can replace A2 with text so A2 here replace with text A2 here replace with text this is my Lambda function now if I want to test my Lambda function within brackets I would just give this as the input and now when I hit enter it gives me three I can check this is working for all these cells so I can now create my Lambda function from this so I would copy this part here actually including the equal to sign so this I'm going to copy let me Del delete this and let me hold the control key and press the F3 key so it opens the name manager here I'm going to go to new and let me call this count Words and here in refers to I'm going to put that Lambda function and now when I click okay my Lambda function is created I can come here I can use the function count words you can see it shows up in the intellisense and it tells me it takes one single argument which is the text I come here I select it and there we have the total number of words in the cell so again I'm hoping you're getting how this Lambda function works you have a formula you can easily convert it into something very very simple so that it takes one two or a couple of arguments and gives you the result instead of the Long Function let me show you one more example of Lambda function which is slightly more complicated and then I'm going to finally show you how to use recursive Lambda which is something that you can only do with a Lambda function it is also a lot more advanced than the functions that I'm showing you right now but it is worth knowing here I have these product IDs and you can see it's alpha numeric characters so there are numbers as well as well as alphabets or other characters such as hyphen and I only want to extract the numbers here in this column now I can use a formula here so let me show you how the formula is going to be constructed so I'm going to use sequence and within sequence I'm going to use Len and I'm going to use this cell here so what it's is going to do is give me a sequence of numbers based on the length of this cell which is going to be five so it gives me five here if I use it for this cell A3 it is going to give me eight because there are eight characters in this cell now here I'm going to use this within the mid function where this is my text and the sequence function that gives me these sequence of numbers is going to be the start number so it is going to take five different scenarios where in the first instance it is going to consider one as the starting position then two as the starting position then three and so on till it has covered all these numbers so this becomes my start number and then the number of characters I want to analyze is one so now when I hit enter it gives me these characters here in different rows now I only want to extract the numers so what I'm going to do is multiply this by one and when I do that the characters that were text or not numbers would give me value error and wherever there was a number it was going to give me that number instead now I don't want these value errors so what I'm going to do is use if error and I would say if there is an error then give me blank else give me that number so it removes the value error but it gives me these numbers and now I can simply combine them by using text join where I would say I want to combine my my D limiter is going to be nothing null character so just uh two double codes and I want to ignore Mt cells and I want to combine all these so now when I hit enter it gives me 2 3 1 and if I drag it for all these cells you can see it works perfectly so let me actually copy this formula and put it here and now we are going to construct a Lambda from this so let me copy this entire formula and you can see this is fairly complicated formula and I'm going to use this for a Lambda now again the first way to approach Lambda is see how many arguments it needs from me and it just needs one which is this cell reference so I would have a Lambda with just one single parameter so let me copy this let's create Lambda where I would just call it maybe text or maybe product ID and uh then I can have the function here and then I would remove this reference A2 with the variable which is PD ID or product ID and that's it that is my Lambda function you can check whether it's working or not by giving this as an input to the Lambda function now when I hit enter it gives me the right result I can drag this down it's working perfectly so now I can put the Lambda function in the back end I would copy this along with the equal to sign hold the control key press the F3 key come here click on new and within new name manager here I'm going to give it a name let's call it get num and I can put the function here so I have this function it looks fine you can double check the function in this case now I can remove this and I can use my Lambda which is get num and I can just click here and hit enter and you can see now a complicated formula fairly complicated formula has become so easy where I can just use one single argument with the function name that I decided what it should be and this is the power of Lambda it just simplified these things so much now I'm hopeful that I have shown you enough Lambda example so that you understand where it can be used how it can be used and the power of Lambda function when working with complex formulas but now let me show you an example which is something that only Lambda can do and it actually makes it very very useful when you want to Loop through cells or when you want to call a function within a function it's called recursive Lambda here I have these names and I have these codes and the full country name here in a separate table now what I want to do is I have a name followed by a country code and I want to replace that country code with the full country name based on this table so if I have Scott comma us then I want to have the result as Scott comma United States and here Harvey comma United Kingdom now just to make things a little more interesting I do not have country codes that are of the same length so these are two letters as well as three letters and I also have this situation where I have the name but I have two name so I want the result where I want Steve comma United States comma United Kingdom maybe this is a person who servicing two regions so I want both the country names now I cannot do this with one single formula in Excel but I can do this using recursive Lambda so what recursive Lambda is going to do is it is going to go through all the cells here in this table and make the replacement now before I get into the recursive Lambda let's say I ask you how would you do it manually if you have to do it without any formula then what are you going to do is you're going to check each of these names then check if the code exist in this list or not and then make the replacement then move to the next cell and then again go through this list make the replacement and then move to the next one so we are going to try and emulate the same thing using a recursive Lambda and another thing I want to show you is before I go into the recursive Lambda thing if I want to do the replacement just for this one country code which is us if I just want to do it for this one country code what I can do is use a substitute function and within substitute function this would become my text which is what I want to replace where I want to make the substitution this is my old code which is the old country code that is there in the name that I want to substitute and these are the new country codes and if I hit enter it has made the substitution only for the us but I want to do it for all the countries so if I'm doing it using substitute function I would have to do it probably four times and I want to do the same thing using recursive Lambda so let's remove this let's create the recursive Lambda and let me first show you how that works so I have some text here in this cell let me change the font color so let me show you the construct of a recursive Lambda when you are creating one there are going to be four segments the first one is going to be the parameter now the parameter here would be the same as the substitute function that I showed you because essentially what what I'm trying to do is go through these names and make the substitution one by one for each of them so I'm going to take three parameters which The Substitute function takes which is going to be the names the old code and the new code or the full country name and then I would have the if condition and this is where the recursive nature of the Lambda comes in because within if condition I am going to check whether I have covered all these country codes or not so I am going to Loop through these codes and then once I've completed these which means when I've completed all these cells and I reach a cell that is blank then I would know that my Loop has completed so my if condition is going to help me Loop through them and while I'm looping if the these cells are non-blank then I'm going to use the substitute function to change these names so when I'm considering us I would change all the US to United States and when I'm considering UK I'm going to change all the UK to United Kingdom and once my if condition is false I would exit the loop so let me do one thing let me first construct the Lambda function and and since this is a little Advanced concept I'm going to do it slowly and I'm going to explain each and every step and once I have constructed the formula I'm going to then explain it again so let's start making that formula and I'm going to do it in the formula bar here so let's make that formula which is going to be Lambda one more thing one more thing before I get into the formula is I also need the name of the function so in most of the cases what I've done is I have named the function when I've reach the stage where I open the name manager and I put the formula and I give it a name in this case I would need that name beforehand so although I'm not putting this formula because I don't have the formula uh as of yet I'm still need that name so let's say that I'm going to call my Lambda replace codes so this is going to be the name of my Lambda function I need this beforehand because I'm going to use it in the Lambda function I construct that is how I'm going to call my Lambda function so now let's start creating the Lambda function here I'm would say Lambda then I need to provide the parameters which are going to be three parameters that I showed you in the substitute function which is going to be names which is going to be these names here then old code which is going to be these old codes or you can call it anything you want I'm going to call it old code and let me call these full country names as new code just for the ease of formula and now I'm going to use the if condition and within if condition I need to check whether I've covered all these code or not so I'm going to say if old code is not equal to blank which means that there are still I've not yet completed this list if that is the case then I want to run my Lambda function so I would say replace codes which is this function that I'm going to name my Lambda because I need to call this here I've used this here and then I would say substitute and within substitute I would have names which would be these names and old code needs to be replaced with new code so this becomes my substitute function and now I'm going to offset the old code which means that I'm right now if my Loop is running for the very first time I'm right now in the US uh cell and now I want to offset and go to the UK cell this is how I'm going to Loop so I would say offset old code by one row and let me break this formula so it's easier for you to see so let me break it here and I would say old code offset and then I would offset the new code by again one row so this is the part that is the looping that is happening and now if this condition is false which means that I've covered all these cells I want to return names now I know this is too much to take in so let me explain what is happening but let me first construct the formula completely so this has become my lamb Lambda formula and as of now if I hit enter it is going to give me calculation error but I cannot even test this formula right now in the cell as I've done with with previous formulas because uh it uses a name called replace codes and Excel has no idea what this is so what I'm going to do is I'm going to take this function first create my Lambda function in the back and give it a name and then explain everything how it works so let me press contr F3 it opens the name manager let me go to new and here I'm going to use the same name replace codes it's very very important that you use the exact same name because if you don't then this is not going to work because it'll not know it'll not be able to call the right function so it's very important that these names remain the same and here I'm going to paste my formula and click okay so replace codes function has been created now I can come here and I can use the function replace codes and you can see it takes three arguments name old code new code these are my names this is here this cell D2 is the old code and here D3 sorry E2 is the new code and now when I hit enter it gives me the right result and you can see it has worked perfectly Scott United States so it works nicely let's see if it works for three digit three alphabet country code so yes it has and it has also worked for Steve us UK you can see it has replaced us and UK both with the full name so my Lambda function is working perfectly fine now let me quickly explain how I did it so let me put my Lambda function here so that I can explain it to you how it works so what is happening in this case is I have this data and I created a Lambda function function and I said this Lambda function is going to take three arguments the names the old code and the new code just this cell and this cell and then I have this if condition where I say if my old code value is blank so imagine that I run this function and this is the first Loop this is the first time this function is running it is going to take these names the old code value is going to be us and the new code value is going to be United States but then it says if the old code value is not blank which is going to be true because this is not not blank then it does the function the replacement where it substit substitutes names as in these names using the old code value which is us with new code which is United States but in that first iteration itself it offsets to the next line which is UK and United Kingdom and then because I have called this function it is again run the loop is again run and it again comes back here but this time the names is going to be the set where us has been replaced with United States already because one Loop has already run and old code is UK now and new code is you United Kingdom now so now it again goes here it checks if the old code is not blank which is it is not because it is UK so it does the replacement again for UK then it moves to the third row and it is called again this time for France it does the same thing it makes the substitution for FR to France and then it runs again in the fourth time it is India it does all the substitutions and when it is run and when it comes to this cell so because it is being getting offset every time when it comes to this cell here which is blank this condition is no longer true so it simply returns me the names and in these names all the substitutions have already been done us with United States UK with the United Kingdom and so on so it finally gives me these results so what I've done is with a Lambda function with a recursive Lambda function I've created some something where it is looping through all these cells and doing some calculation and when the given condition is true it keeps on looping and keeps on doing the calculation and as soon as the condition becomes false it goes out of the loop so this is how recursive Lambda works these are very powerful and although it is a bit Advanced and it takes a little bit of time to get used to but once you identify and you understand the way it should be used you can do some pretty advanced stuff with it some pretty Advanced Data cleaning or data manipulation in your Excel using recursive Lambda I know this video has gotten a bit long and I've covered so many different examples but I hope that by now you have a fairly good understanding on how to use the Lambda function in Excel so even if you're not using recursive Lambda you can still use Lambda to simplify your work by converting complex formulas into something simpler and you can give it your own name so you can create your own functions and make your work easier that's it in this video I hope you found this useful also if you're liking these videos please subscribe to this YouTube channel and click on the Bell icon so that you never miss out on any new Excel tips video I come up with thank you and have a nice day

Channel: TrumpExcel

Views: 9,096

Keywords: Excel Lambda Function, Lambda, Lambda function, Excel Lambda, Recursive Lambda Function in Excel, Recursive Lambda, New Excel Functions, Lambda formula in Excel, Excel Lambda Formula, lambda formula, excel lambda examples, lambda function example, advanced excel, excel, excel basics, excel tips, excel tutorials, learn excel, sumit bansal, trump excel

Id: 9G4IIYHySoc

Length: 31min 10sec (1870 seconds)

Published: Thu Mar 28 2024