Formula nesting: Using multiple functions in the same formula

- [Instructor] When you work with functions in Excel, particularly if you're trying to clean up data, sometimes one function alone is not going to get the job done. And so it's not uncommon to use more than one function. We also, as we use functions inside of functions, refer to the concept of nesting. In this worksheet here, I've got some data in columns A and B. And you can see there's some oddities, some unusual spacings. And also all the data's uppercase. Not truly wrong, but let's say we only want the first letters to be capitalized. So if we were focused only on column A, we might start the process of cleaning this up by typing a function called proper.

And referring to cell A2 in this case. That's what it would look like. But we also want to combine that with the data in cell B2, and along with it, let's also consider the idea of cleaning up the spaces. So how do we pull these pieces together? Along with that last name, we also want to get out of this the first name, but we want to separate these by a comma and space. It's not uncommon to see, for example, an entry Wagner comma space Max. So following this double quote, comma, space, double quote.

And the entry right here. Now we want that to be proper as well too. So rather than putting proper just on A2, let's take out the parenthesis here. Do our combination. Right parenthesis. And we see how that's looking. But we still have some space issues and that's a function called trim. Now we can put this in front of the word proper, as I'm doing this here. And we'll need another right parenthesis.

And that cleans up the data. Copy this down the column. So we're using multiple functions together. Now possibly, we could do this in a different way. There is a new function available in Excel if you're using Excel 365, a new function here is called textjoin. So instead of pulling together the data this way, we could use this new function called textjoin. And textjoin begins with a delimiter. So the delimiter in this case, and we're only using this with two cells, so the example's a little bit contrived here, let's say.

But if we had a middle initial column or possibly we're gathering data from multiple cells, this is going to be even more powerful. But we're saying, let's first of all, define a separator here, comma and a space. And if there are any empty cells, let's not worry about those, so we ignore that. By putting in true we're going to ignore empty cells. We can either type true or simply put in the second comma here, that's the default setting that's equal to true. And then we put in the cells we want. And so instead of having this entry, we'll simply drag across these that way.

And now, one additional closing parenthesis here. We've got three functions used together, and we see that result. The goal in all these cases is not to say we're using more and more functions. That's not really a badge of honor, but the idea is sometimes we use functions within functions. And, of course, the more functions you're familiar with, the more likely it is you are to use these techniques. So this is going to work right here. And also as I double click to copy this down the column, all these are falling into place. And once again, that's what we're doing here, we're nesting functions.

And in a certain sense, what happens, although we can't watch this while it's happening, this takes place first and then that information is then embedded within proper, and then that information is embedded within the trim function. And in some cases, and this would be the case, we could put trim right here and proper over to the left. So let's change that to show how that would work. We put trim there and then proper over here. Complete that entry. It looks the same. Copy it down the column, and those are all looking the same as well too.

But different techniques there. Now, off to the right we've got some other data here. Let's say we're going to give a bonus to these people based on their job rating. And we need to use the if function. I'm making the column wider so we can see the function better. Equal if. A simple example here would be, the company's decided to give a bonus if the job rating here is greater than three. In other words, if it's four or five, give these people $2,500, otherwise nothing. Fine, a simple if. Complete the entry.

Copy it down the column. And we're expecting to see the 2,500 only when the job rating is four or five. Now imagine the company changes its mind and says, in effect, "we only do this for people "who are full time." So, in addition to this requirement here, that you've got to have a good job rating, let's also include another requirement. And we will embed this within the and function. So we're about to say two things. The and function could contain as many as a 127 different conditions here.

We're only using two conditions. So this condition needs to be true, as well as, the status, that's in cell K2, must be equal to Full Time. By the way, this is not case sensitive but you got to put the space in for sure. And most of the time you would make it appear to be case sensitive anyway. There we are. So now we're changing this. We've expanded the rule. We only give bonuses to full-time people, but you must have a good job rating as well. So this time we're going to see fewer entries here. And this person here has a good job rating, but is not full time.

This person here is full time, doesn't have a good job rating. And in the list that we're seeing so far, this is the first time where we actually see both of those conditions here are being true. And we could possibly instead, although the logic might elude the people who are the beneficiaries here, if we say or, we're saying if either condition is true. It's a happier decision, based on the recipients. Now we're saying what? If either condition is true. So using and and or sometimes inside of an if makes perfect sense.

Now another perk to all this too could be, maybe we want to have alternate answers here based on different job ratings. So, simplifying this for the moment, but taking it in a different direction, let's remove the or and the requirement about full time and say, if your job rating is four or five, you're going to get $2,500. But what if it's not true? We can put in another if, an if inside of an if, a nested if, to check and see if this job rating is equal to three.

And if it is, let's give these people $900, otherwise nothing. That's an if inside of an if, a nested if. We need an additional parenthesis on the right. Since these are all highlighted I can press Control + Enter. They're all done and now we see a couple different entries. Once again, the function looks like this. And if we go a step further and also include logic that includes a check to see if the job rating is two, we'll put that after the 900 here. Another check. An if inside of an if inside of an if.

If this job rating equals two, Comet will give these people $99. When that's not true, we'll give them zero. And at that point, the only case where it's not true would be those with a job rating of one. We need three parentheses on the right now. The number of open and closed parentheses must be the same. This is an if inside of an if inside of an if. And we've got four possible answers now. The maximum number of nested ifs in older versions of Excel was seven. And believe it or not that was changed Excel 2007 to be as much as 64.

And that includes all kinds of nesting, not only the if function. But a maximum of 64, which is pretty incredible. And I'd hate to see a function here that had that many nested ifs. This is only two nested ifs. But nevertheless we see possibilities here where we use functions inside of functions. And here they're all if functions inside one another. Now another case here on a different worksheet called MasterList. Imagine, I've got a list somewhere. It's got a bunch of Social Security numbers. I can't change the order of these because other formulas work off of these.

I've got another list somewhere else, possibly on a different worksheet. I simply want to find out if this Social Security number exists in the list. Function here that I would use is match. And by itself, I can simply use this to test this Social Security number and see if it's over in column C. And when you use the match function, often you're looking for an exact match. That's where you put in the number zero here. If we find this, we'll find the location within column C where the match occurs. And the eight there is in the eighth position of column C, corresponds with the row number.

There it is right there. You can see it, it's for Terry Malloy. That's the match function. But what if it's not found? Suppose one of these entries, I'll make a change to one of these numbers here. Now copy this down the column. What do we get? Looks like one of them was not found. And there's nothing wrong with that. I mean that's what we would want to have here. But on the other hand, suppose you say, well, we'd like to see a phrase here like not found. So let's change this and embed this within another function called iferror.

This function allows us to say, in effect, if this is going to cause an error, we will decide what to put into the cell. Comma, how about the phrase Not Found? Otherwise, do perform the calculation. So that's relatively concise, but we have embedded the match function within the iferror function. In the first case it does everything it's supposed to do. And all the other cases it's going to be the same, except in row four. And we see what's happened.

Once again, look at the logic here. The iferror function says, "If this calculation right here, "if that's going to cause an error, then display Not Found. "If there is no error, perform this "and come up with the appropriate answer." So we see how that's working properly. Now, nothing wrong with all this, but now suppose in addition to this, what we really were after all along was the person's name. What we really want to do with this match function is to use it as part of another function called index.

If you're familiar with the index function, you know that it allows us to pull data out of a table based on a row and column reference. So, I'm going to now embed the index function. The index function says, "I've got a list somewhere. "Where's that list?" It's in column A. Comma. I need to pull information out of column A based on a row reference. Earlier we found out that this particular Social Security number here was in the eighth row, so we want to go into column A and pull data out of the eighth row.

Now when you use the index function, you need a row reference after you put in the table. And then comma. And a column reference. But in the example here, if we're only looking in one column, and at that point we're only looking in column A, we don't even need the column reference. So we'll simply put in a right parenthesis here. Now if you've never used the index function, this is a bit of a stretch right here. But we should be getting an answer here. And it's Terry Malloy. And that's what we would have expected based on the data. But as we look at the formula here, it's getting longer.

And again, the goal is never to say we're using more and more functions together. But it does illustrate the idea of how we sometimes pull functions together by using them in different nesting ways. Now, double click, copy this down the column. We still get the entries this way. Now, sometimes when you want to expand a formula, it gets pretty dense when you're trying to make existing changes. So another technique might be, let's use this and hold onto it for the moment. But over here let's say that we wanted our answers not to be all uppercase.

We could type equal proper. Left parenthesis, click here. And you can expect what the answer's going to be. Terry Malloy, with only the M and the T capitalized. That looks just fine. But do we need to do this separately? No we don't. What could we do here? Well, this function uses F2. Let's go over to cell F2. Double click, highlight all this. Press Control + C to copy. Escape. Double click in here to edit, and paste it in here.

Control + V. Now we certainly could've typed proper and done that over in cell F2 to begin with, but by doing it this way, we're illustrating the concept of how we sometimes work with functions together and sometimes we build them in stages and then substitute. That's a long formula right now, involving, from left to right, the proper function, the iferror function, the index function, the match function. And again, the goal all along is not to be saying we're using more functions, but just to illustrate our needs sometimes dictate the needs of multiple functions here.

And we could even go a step further here and only pull off the last name. That would involve using a function, for example, left. It might also involve using the fine function, and other functions as well. But I think this gives you an example of how we can use nested functions and based on this in our prior examples, how we can use these functions effectively together. Nesting of functions.