Hi Nicky. It's been such a great journey. I'm quite sad that this is our last week. Hi Prashan, I know what you mean, but we're going to finish on a high with some really exciting content. Zara's data is now fully prepared. How are we going to help her finish off for reconciliation? We're going to let Excel do all the hard work by employing the help of it's logical and lookup functions. Let's start with discussing logical functions first. Can you please give us some examples of the logical functions we'll be looking at and suggest why they might help us with data analysis. We're going to start with the best-known and arguably most useful logical function, which is the IF function. We're then going to move on to taking a little look at its helpers, the AND and OR functions. As we'll see in the videos, all these functions are really useful for performing data transformation, cleaning, and of course, making determinations. The IF function is definitely one of the more useful functions in Excel with a wide range of applications, but it can be a bit confusing for people who haven't used it before. So could you please talk us through the structure of an IF function? Good idea. So let's say I have a price list, but if the customer is a gold partner, I need to apply a five percent discount. I'm going to use an IF to make this determination. The first argument with an IF is always a logical test. This compares two values using a logical operator. So in my example, I'm going to check if customer type equals gold. This will either be true or false. The second argument in the IF is what to do if the logical test returns true. In this case, I'll subtract the five percent. The third and final argument is what to do if the logical test returns false. In this case, really easy, I just charge the usual price. That's great for true or false situations. But what if I have three outcomes? Let's say silver partners get a three percent discount. Well, the solution here is actually to use another, IF. This is called Nesting IFs, and we'll see it in the videos, but basically it works like this. The first IF checks, if it's a gold partner. If it is, easy they get five percent. If not, the second IF is going to check if they are silver partner, if they are, they're going to get three percent, otherwise they're paid the regular price. Now, will we be looking at other logical functions like the IFERROR function? Good catch. Unfortunately we don't have time in the videos, but IFERROR is a useful function, so we've included it in this week's toolbox, and it is required for the practice challenge and the assessment. So learners don't forget to check it out. Great. Let's talk about LOOKUP functions. Well, the first ones we'll look at is the VLOOKUP. These are widely used in business for connecting data sets and for error checking. But they are often criticized for being inefficient. Should we be looking at VLOOKUPs? VLOOKUPs do have their drawbacks, but Microsoft have actually recently overhauled the VLOOKUP and it's now super fast even on large data sets. VLOOKUP is comparatively easy to learn and actually does have its applications. So given now that it's a widely used in business, I think it's important for everyone to know it. Now, we actually have two videos on VLOOKUPs. The first on categorizing and the second on matching. Can you please explain the difference here? Yes. In the first video, we're going to look at what's called a range lookup. This is less well-known, but a really good option that allows us to categorize data. So rather than doing an exact match, it looks to see what range and number falls within. This is ideal. For example, if you were trying to say grade papers, you don't want to have to provide a lookup table detailing every possible grade that students might get. You just want to say if it falls between 70 and 79, they get a B. That's where you'd use a range lookup. The other video focuses on the more commonly used exact match VLOOKUP. This is where you want to look up a specific thing like a Customer ID, and return a corresponding value like the Customer Name. As you say, VLOOKUP is one of those functions you have to know. But it does have the limitation that you can only return the value to the right of the lookup column. So can you please suggest an alternative? I can. A brand new function called XLOOKUP has recently been introduced and it addresses all the limitations of the VLOOKUP. It works vertically and horizontally, and it even allows a search on wildcard characters. So really exciting and we will take a quick peek at it. I think the XLOOKUP, may eventually replace the VLOOKUP, but a lot of people don't have it yet as it's only being rolled out to Office 365 users at this stage. So what other options are there? Well, that's a question our last video we'll answer. We're going to look at two very versatile LOOKUP functions called INDEX and MATCH. They can be used independently, but typically we nest the MATCH inside the INDEX, which gives us a very flexible lookup. With two matches, you get a potent two-way lookup. It's going to be a busy week. Any last words of encouragement for our learners? Yeah, be patient with yourselves, learning is a process and practice, practice, practice. Because practice makes permanent. Thanks Nicky. So let's recap the topics you'll be covering this week. Performing logical operations with IF, applying advanced logical operations, categorizing data with VLOOKUP, matching data with VLOOKUP and XLOOKUP, and then advanced data matching with INDEX and MATCH. You've got another great video for each topic and some challenging quizzes for you to test yourself. Don't forget to check out the toolbox as it will help you with this week's practice challenge. You're now on your way to becoming an Excel ninja.
