Welcome to week five, and welcome Nicky. >> Hi, Prashan. >> This week, we're going to be looking at advanced LOOKUP functions in Excel. Now, in a previous course in this specialization, we've already looked at the LOOKUP functions CHOOSE, VLOOKUP, INDEX, and MATCH which are already advanced functions. So do we need to get any more advanced, Nicky? >> Quite possibly not. But at the beginning of this course, we said it was really important to make sure you use the right function for the job. And we want to make sure that our learners know about all the options, so that they can make an informed decision. It's also possible that they may encounter some of these advanced LOOKUP functions. In workbooks, they come across in the workplace, and they need to know what these functions do and how they work. And most importantly, we want our learners to be aware of what the advantages and the disadvantages are of these more specialized functions. >> So what are we going to start with this week? >> We're going to start with the INDIRECT function. This is a curious function because it allows us to indirectly refer to cell reference. In other words, normally, when we refer to a cell, we get the value coming back from that cell. But with the INDIRECT function, when we refer to a cell, it expects that to have an address and we get the value back from that address, so quite interesting. Now, generally, it works with regular style addresses, like A1 style addresses, but it also has the capability to work with R1C1 style addresses. >> So what is R1C1 referencing? >> Well, when we talk about our regular addresses, we all say the column letter and the row number. R1C1 just lets us specify the row number followed by the column number. But this is quite handy when you're dynamically calculating a position in a workbook because very often, you'll end up with a column number rather than a letter. So what are some of the applications of the INDIRECT function? >> They're quite a few applications. For one thing, if an INDIRECT refers to a cell, it effectively locks it. So for example, if my INDIRECT referred to C2, if I inserted a row above C2, regular functions will then start referring to C3, but the indirect will stay doggedly referring to C2. And that can be quite useful when your workbook is changing. You can also get some fantastic results when you combine it with named ranges. So for example, we're going to see in the video how we can create cascading, drop down lists, very easily, just using the INDIRECT function. >> Now, I always had this discussion about the INDIRECT function being powerful, and then others say that the INDIRECT function is just evil. What's the context of this debate? >> The truth is, it's both. It is really powerful and there are some problems that could be more easily solved with INDIRECT rather than other functions. But it has two very serious drawbacks which we need to be aware of. The first is is what's called a volatile function. Excel, by default, uses smart recalculation. So when you change something in a workbook, it works out which cells are going to be impacted by that change and only recalculates those formulas. But where you have a volatile function, they get recalculated regardless. So if you have a lot of these functions in a large workbook, you might see a real performance impact. The other problem with the INDIRECT is it's hard to audit. Because of its very nature, letting you specify where the inputs are coming from on the fly, it's very hard to detect when an error creeps in. So those are the two reasons why you might want to think about staying away from INDIRECT. >> Now, I often see the INDIRECT function being used with the ADDRESS function. Will we be looking at that? >> Absolutely, the ADDRESS function allows us to construct an address and that can be an R1C1 or an A1 style, so it's a natural partner for the INDIRECT function. And we're also going to look at some little helper functions that often go alongside them and that's the ROW, ROWS, COLUMN, and COLUMNS functions. >> And then, later on in the week, we're going to be looking at the OFFSET function. >> Absolutely, and the OFFSET function is a really powerful function, but quite simple, really. What it does is given a specified cell reference, it will return a reference or range, a certain number of rows or columns away from our original starting point. So it has great lookup potential, but what it's mostly used for is creating dynamic ranges. And from this, you can actually create dynamic charts and quite a lot of other applications, so really useful function as well. >> Any downsides that our learners need to be aware of with the OFFSET function? >> Unfortunately, yes, similar to the INDIRECT, the OFFSET is also volatile, so probably best to avoid it with large data sets. And it also has auditing problems in that if you were, for example, to trace precedence, it will only return the original reference. So it has its drawbacks. >> So some advanced yet very useful functions in Excel for looking up. Thank you so much, Nicky. Now, we've got some practice videos coming up for you. Make sure you check them out and download the Excel workbooks, so that you can work alongside us step by step. Check out this week's quizzes, the practice challenge, the tool box, as well as this week's great ninja tip. Now, it's over to you.
