Welcome to week three. Welcome Nikki. Hi Prishon. This week we'll be solving business problems using Excel's lookup functions, starting with the CHOOSE function. Now Nikki, the CHOOSE function is not a function that most people have heard of. No, which is quite nice. So, we're going to introduce you to something a bit different. It's also a handy little function but it's quite easy. So, a good way to start getting familiar with how LOOKUP functions work. Then we'll take a look at the ever popular VLOOKUP function. Yes, and the VLOOKUP function is one of those you just have to know. It is used very extensively in business and it's one of the sought-after skills when applying for a job. So you want to have that in your toolbox. So VLOOKUP or vertical lookup. Can you prime our learners about how it actually works? With pleasure. So I'm pleased you mentioned the vertical lookup. That's what the V stands for. You need to have your data organized vertically to use the VLOOKUP. If your data's organized horizontally, there's assistive function called the HLOOKUP which works exactly the same way, but because most data is organized vertically, we're going to focus on the VLOOKUP. Now imagine you have a telephone book, and you know the person's name but you don't know the telephone number. What you're going to do is go through the phonebook till you find the name. When you do, sitting next to it would be their telephone number and possibly their address as well. Well that lookup process, the VLOOKUP automates it for us. Sounds very simple, and it's used extensively in business, how come? Well, it's actually really versatile. So, we could use it the way I just explained. So you might have a part code and you need to get the description, for example, but you can also use for categorizing data. You can use it for reconciliation, you can use it for error checking. So there are just so many applications for it. We actually got two practice videos on VLOOKUP this week. Yeah. That's because the VLOOKUP actually comes in two slightly different flavors. We've got what's called a range lookup, which allows us really to categorize data. So, we might want to look for a value in the range 1 to 10 or when the range 11 to 20, and that's what we'll use our range lookup for. More commonly used though is the exact match. That's where you have a part code, one employee id and you want to get back some specific information related to that exact value, and that's where you use the exact match. They are slightly different. So we're going to take a little bit of time to look at each. Now, all these complaints about the limitations and how VLOOKUP can be quite slow, where's that coming from? Look, the VLOOKUP does have limitations just like any function. It is important to be aware of those. So, for example, when you come to do a lookup, your lookup value, the thing you're trying to match must appear to the left of the value you want to return. If it doesn't, you simply can't use a VLOOKUP. Also, if you have really large datasets, but I'm talking like hundreds of thousands of records, and you've got a lot of VLOOKUPs, you will start getting performance problems. It's doing a lot of work. So, there's no getting around that. But you might want to look at an alternative. These alternatives are INDEX and MATCH? Absolutely. You do find the two normally go together. But MATCH can work on its own or can be combined with other functions. So we'll start with just looking at the MATCH, and then we'll look at combining the INDEX and MATCH to solve slightly more sophisticated problems that maybe the VLOOKUP isn't so well suited to. So, should our learners be focusing on VLOOKUP or INDEX and MATCH? Really it's about suiting the function to the problem. VLOOKUP, the range lookup is very efficient, very useful if you need to categorize data. Also the VLOOKUP is well-known, it's easy for learners to understand. So, if you've got smaller datasets, and you just want to lookup a value, VLOOKUP might still be a better option. However, if you're trying to look for a value that's to the left of your lookup value, or you're working with massive datasets or maybe you need to look up a column and a row index, then you may find that the INDEX function is about alternative. So my recommendation is, make sure you're confident using both and fit the function to the problem. Sure. Sounds exciting. A little bit complex which means a lot of practice for our learners. Yeah, and that is one thing I'm pleased picked up on. If these functions are completely new to our learners, they do take a little bit of practice to get your head around. So, don't be put off. Have a go, try again, keep practicing it will come. Thank you for the great advice Nikki. We've got some practice videos coming up for you. Make sure you download the Excel workbooks and work alongside us step-by-step. Come back and practice these again because practice makes permanent. To test your skills in a different context, we've got a great practice challenge for you as well. Check out this week's toolbox as well as this week's great initiative. Now, it's over to you.