Most people think the amperand is just for joining text in Excel, but what if I told you that's just scratching the surface? It allows you to make flexible formulas, dynamic titles, and custom control panels. So, let's find out what the amperand can really do with five practical examples from easy to hard. Let's get into it. The first and easiest example is to replace the concat function in Excel. Let me show you how that works. Suppose over here we've got the first name and the last name and we want to combine these two into the full name. And if you want to follow along with this Excel file, you can download it for free in the video description. So all we would need to do here is type equals concat. That's the function we would typically use and that's the first name and here is the last name. But in reality, we can also just do that with the amperand just by typing equals. And we'll simply select max, put an amperand, and select the last name. That really does the exact same thing, but it's probably a bit faster. That said, right now, we don't have any separation between the first name and the last name. Ideally, we'd like to have a space in between. So, you might think of using the equals text join function instead, where the delimiter is basically what do we want as a separator. So we would just put a space. We can just ignore empty cells. And this is the text one and the text two is the last name. Hit enter there. So you can see there's nothing wrong with the text joint function. But in fact the amperand does the same thing but a lot more easily. Let me show you how. I'm just going to type equals and max is the first part. Then I'm going to put the amperand like before and instead of selecting the last name directly in quotations, I'm just going to put a space amperand again and the last name on the end there. Hit enter. And now we have that same max stubben. And we can just double click on the side to drag it all the way down. If you want to see the formulas, you can just use a shortcuttrl and back tick. And here we can see we've got all of the amperands. To go back, just press Ctrl and back tick again. If you haven't seen this feature before, it's the same thing as going over to formulas and clicking on show formulas to see what's actually inside. Admittedly, that was a fairly basic scenario. So, let's go over a much more impressive feature, which are dynamic titles. Here we've got a small report that has the analysis of this company called Pegasus in 2025. And we've got some breakdowns for each of these sections. That said, I would ideally like to have this as a dynamic title. So, if I change this company to Nike, then the title should update. But right now, it's not doing that. And we can fix it with the amperand. So, all we need to do is type an equal sign up front. And in quotations, we want to put everything that's text. So, analysis of and a space. We want that as text. And then we're going to put an amperand and select the first cell, which is going to be the C4, which is the company name. Then, we can put another amperand. And we're just going to add a space. So in quotations, we just need to put a space. A final amperand. And now we'll select the 2025. We can delete the rest. So all of this part right here. I'm just going to delete and hit enter. So we've now got analysis of Pegasus 2025. If I change this to Nike and 2020, you'll notice all of the data updates for that too. And we can do the same thing with all of these subheaders. So I can put an equal sign here and it's going to be now the revenue breakdown. Make sure I add a space and amperand Nike. So, it's going to be revenue breakdown Nike there. I can do the same thing with the cost breakdown and the customer subscriptions too. Let me just make sure I put the actual 2025 year as well. So, I'm going to put that in quotations and select the year, which is 2020. Right now, this is actually a very common practice in finance and consulting. Let me show you an example with a three statement model and a discounted cash flow. So you'll notice here that we've got the three statement model for Apple. And here we've got the same input of the company name. Then if we go to the next tab for the discounted cash flow, we've also got the Apple, we've got Apple share price. And over here we've got Apple weighted average cost of capital. So now if I were to change this to Nike, you'll notice that not only it updates on this first sheet, but it also updates on the share price, on the title, as well as on this other sheet. To do that, it's simply the same thing that I've just shown you where we put an amperand and in quotations, we want the text. If you're wondering what this first part is with the income statement, it basically means that it's referencing the C4 from this income statement tab. Going back to the report, and we now have a chart, as you can see with the revenue breakdown. That said, for this particular title, it's currently not dynamic. So I can change this to Nike, but really nothing is going to update on this side, even though all of the different subheaders do update. So ideally, I want to make this dynamic, too. And for that, all I need to do is select the header. Make sure you don't put an equal sign in here. Instead, you want to put it on the formula bar up top. I'm going to put an equals. I'm just going to link it to this cell right here. We've now got revenue breakdown Pegasus 2025. If I change this to Nike and 2015, you'll notice it's not just the headers, but also the chart title updates as well. Surprisingly, few Excel users realize that the amperand also makes Excel formulas flexible. For example, here we're looking for Max's revenue in the month of February. For this, you might think of using something like an X lookup. So, let's see what happens. As the lookup value, we're looking for Max, lookup is where can we find Max? We can find him in the list of employees over here, comma, and the return array is what do we want as the answer? We're looking for Max's revenue. So, we'll select this part right here. Close up parenthesis and hit enter. That seems easy enough, but when we actually look at the data, the value we're getting right now is this one right here, which is the month of January. It's not the one for February, which is what we would want to see. That's because the XLOOKUP currently isn't referencing the month at all. If we look at this very first part, the lookup value only looks at max. It doesn't look at the month as well. And by default, the XLOOKUP formula can't account for two lookups. But with the amperand, we can work around this. So all we need to do is add an amperand here at the start and select February 2. And the lookup array as well, we need to add an amperand here at the very end of it. And also select the whole month column. So all of this part, hit enter there. And now we have the right data. I can change this to the month of January. And you'll notice that updates to the correct data, too. So, we've managed to make this XLOOKUP a lot more flexible just by using the amperand. And this also applies to the index match formula. So far, we've been looking at exact matches like an exact name or an exact month. But with the amperand, we can also use it to create approximate matches. Before I show you this impressive feature, if you're interested in working in any of these roles over here, you should consider taking our complete finance and valuation course. First, we cover financial statement analysis using Apple's real annual report as an example. Then, we get into financial modeling through a three-statement model on Apple. After that, we begin the valuation phase where you learn to do a discounted cash flow, a comparable company's valuation, and a precedent transactions valuation on Apple. looking at their real financial statements to eventually derive a valuation range. Lastly, we'll show you how to present your investment thesis using a stock pitch format. So, if you're interested in checking this out, head over to the link in the description below. All right, let me now show you how the approximate match works with the amperand. So, over here we've got this list of data for the clients. This case, we're looking for Nike and we're looking for Nike's revenue. That said, Nike isn't exactly called Nike in this data set. It's called Nike Inc. So when we try to do just a normal X lookup, we look for Nike. We look for Nike within this area and we want the revenue to be returned. You'll notice that it doesn't actually work. And that's because we don't have an exact match. Instead, we only have an approximate match. So let's see how we can accommodate that by using the amperand right here as the lookup value. All I need to do is put an amperand and in quotations I'm going to put an asterisk like this. That basically means to ignore everything that comes after. As long as there's a match with the beginning part. So Nike is Nike here. Then it should be okay and return the relevant answer. Then at the very end, we need to get to this match mode area. So I'm just going to put a comma there twice and we get to the match mode. We're looking for a wildcard character match. That's basically the asterisk part. So, we're happy with that. I just need to put a two there. Close up parenthesis and hit enter. You'll notice it's now matching for Nike. If I were to put Zara in here, even though it's called Zara Home, it's still able to find the right data. At this point, you might be wondering, but what happens if you have the wrong data up front? For instance, here instead of being called Nike Inc., let's suppose that it's called the Nike Inc. Or maybe it's just that it's got a space in front of it. So, I can have space and then Amazon UK. What happens there? Well, if I try to go find Nike in here again, you'll notice that it no longer works. And that's because if we click back in here, this amperand and the asterisk is only at the end. But we can easily add it up front as well by adding the quotations, the asterisk, and the amperand again, and hit enter. Now, we're starting to see Nike again. Even if I go for Amazon, it's still working well despite the space up front and the UK at the end. The final use case for the amperand which I find most impressive is to create a custom control panel. For example, here suppose we have some sales data for 2023, 24, and 25. All of these have the same number of rows. With that in mind, I want to create some kind of small control panel like this where whenever I type the name of a worksheet like sales 2023, I'm going to be able to see the total sales for it. As the step one for the formula, we need to make sure the naming is dynamic. Equals in quotations, I'm just going to put an apostrophe and then amperand. Select the relevant sheet. So, we're going to select that based on this criteria right here. Amperand again. And now in quotations, we need to put an apostrophe first, followed by the relevant range for the total sales, which is C3 to C18 across all of the different worksheets. Let me make sure I put an exclamation mark over here, which you'll learn why in a second. Close those quotations and hit enter there. Now, you can see we have the exact name of the sheet. Basically, whenever I reference something in another sheet, like let's say this particular cell, you'll notice that it always generates this top part with the apostrophes and the exclamation mark. That's basically what I'm trying to replicate in here by using the amperands. So, that's one part done. And the ranges, the C3 to C18 basically refer to all of this part right here, which is the same across the different worksheets. The next step is to convert this from just a text string, which it currently is, into an actual real reference. And for that, we can use the indirect function. So, I'm going to put indirect in here and simply close the whole formula. That's all I need. There we have all of these values, which basically relate to the sales in 2023. Now, we can just wrap all of this part with the sum formula and just close it at the very end. So, we see one total value like this. If we've done this all correctly, when I go to sales 2024, it should all update to the 2024 figures, which are these ones over here. That seems to be working really well. And the nice part about this is if I add a new worksheet by pressing controll and dragging. Let's say I change this to 20 26 this time. I'm just going to make a number very very big. So, let me just put it right over here. And now if I go back to the control panel and change this from 24 to 26, we don't actually need to update the formula at all. It automatically accounts for this new worksheet. For all these reasons, the amperand is one of the most powerful operators in Excel. But you should also know about the hash sign. If you want to learn more about how it works, watch this video over here, or you can take our Excel course over here. Hit the like and that subscribe and I'll catch you in the next