Okay. Good afternoon, everybody.

Or I guess it could be good morning or good evening, depending on exactly where you're calling in from. I'm based in the UK, so it's just on 2:00, and it's a lovely sunny afternoon here.

So I was just going to wait about a minute or so, but just while we're setting up, just to get any more people. Yes, I can see a few more people arriving, which is great.

As you can see, this 30-minute session is all about data extraction using Excel. So we're going to dive straight in and just basically open up some Excel spreadsheets. If you want copies of the spreadsheets, then you should have seen them on the way in on our normal login page. But if you didn't, in the chat box, I've put the URL to that website, and if you want to go there, you'll see a number of spreadsheets, about five or six spreadsheets. Grab those, download them.

We're not going to use all of them.

It's only a 30-minute session. But if you want copies of those spreadsheets, then just grab them from that URL in the chat box. And it's one minute past, so I'm going to crack on. As you can see, my name's Phil Sparks, and I'm a full-time trainer at Financial Edge.

Been here for about five years and spend my life basically doing lots of Excel, lots of finance, lots of valuation for lots of the big investment banks.

Okay. I'm just going to put in the chat box.

I can see another couple of people have arrived, so apologies for putting in a number of times, but if you want copies of the spreadsheets we're going to use, if you grab the link from the chat box, you'll see a number of spreadsheets there. So without further ado, let me stop that. Let's get Excel on the screen. There we are.

And let me also just open the chat box.

So one last time, just I can see one more person just arrived.

I'll just put the link to the Excel spreadsheets in the chat box if you want to download those to follow along with. Okay.

So we're going to start straight away.

We're going to dive into this spreadsheet here, and you can see, if you look carefully at the name, it says Data Extraction Workout One Empty. So if you want to open the same spreadsheet, you'll be able to follow along. I said, we're not going to do all of the exercises in all of the spreadsheets. We only have 30 minutes, so we need to keep up the pace.

But I'm going to dive into the first sheet, the first tab, and it's called Lookups One.

And just need to get my notes. Zoom, unfortunately, always rearranges my screen when I start Zoom. So there we are. Let me just minimize that.

Apologies. It's just very helpfully rearranged all my screen. There we are. Okay.

So we've got spreadsheets on the screen at the moment, and basically what we're talking about here, the issue we're talking about is that we have lots and lots of data in a spreadsheet, and we need to select from that. We need to find an item and select some information from that.

So it could be in our training environment, we could have lots and lots of lists, big long lists of students, and we might have, say, their exam results or their progress through a qualification stored on a spreadsheet on various lines for each of the individual students.

And we want to find a student, pull out their information. How do we do that? Well, the first thing we're going to use is we're going to use a fairly traditional function, a function called VLOOKUP.

And VLOOKUP has been around for pretty much ever since I started using Excel about 30 years ago.

And it's a little bit simple. It's a little bit clunky. It's not very flexible, but everyone knows how to use VLOOKUP. So let's just see how we use that.

So what VLOOKUP basically does is it looks for an item, and in this case, it's that 25,000. It's someone's earnings.

And it looks in the left-hand column, and it tries to match that 25,000 to the left-hand column so it can work out what the tax rate is.

And in this case, the person should be taxed at 30%.

So underneath, we're going to say equals VLOOKUP, and V is for vertical. There's an HLOOKUP, which works horizontally.

In reality, I've almost never used that.

But VLOOKUP is very commonly used.

So it gives us the syntax. So we start typing VLOOKUP, and it says lookup value. Well, we want to find the 25,000.

That's the income, and we might want to change that.

We might want to find someone else's tax rate, put 10,000 in there.

But the formula is looking for this number, 25,000 in this case.

The next thing we need to do is it asks for the table array.

So we need to highlight the entire table, including the left-hand column, which is the thing that we're going to look up, the income level, and then the right-hand column, which is the tax rate, which is actually what we want to come back with. And then it says, well, what do you want to come back with? Having found the 25,000 or the nearest number on the left-hand side, what do we want to come back with? We want to come back with column two.

The columns are numbered one and two.

And of course, you could have four, five, six, seven columns wide.

But in this case, there's just two, and they're numbered one and two.

So we want to just hard type number two in here.

We hit Return, and it comes back correctly with the 30%. Now, if I made that, say, 5,000.

Let's just check it works. It does. It comes back with 15%.

The interesting thing is that it doesn't have to be an exact match.

So what actually happens is it looks for the number just below Your 25,000 and it won't change until we get to exactly 30,000. When we put 30,000 in here, then it changes to 34%. Okay? So that's VLOOKUP's default behavior, which is an approximate match. It looks for the last number just below where you are, until you actually breach that number.

I can just see a couple of people have just popped in.

I'm just going to put in the chat box a link to the spreadsheets that we're looking at. Okay? So basically, if you want to follow along, download the spreadsheets from that URL. It's in the chat box. Okay, let's go on to the next one.

Now what we're going to do is we're going to look for an exact match, not an approximate match. So we start with VLOOKUP again.

We start typing VLOOKUP, and in this case, what we want is we want to find that product number, A134. And we can see it's actually up here. It's the first one in the list.

Again, we highlight the entire table. There it is.

And again, what we want is the second column.

We want the price associated with that particular product.

But if we put a comma, you will see there's another optional entry within the VLOOKUP, and it says, "What sort of match do you want?" Now, if you don't put anything in, it defaults to an approximate match, which is what we did in the previous numerical example.

Here, I want it to match perfectly.

So I can either jump down to false, and accept that, or I could put a zero in here. Zero in Excel speak is the same as false. A one is the same as true. Hit Return, and it comes back with 3.5, and that's correct.

Again, if I change it to X212, you can see it now changes to this other one, 4.8. Interestingly, it's case agnostic. It doesn't care whether it's uppercase or lowercase.

It's fine with lowercase X212. However, if I do put just one space on the end here, then it comes back with an N/A.

So it actually does have to match exactly, although it doesn't really care about the cases. Okay? Problem with VLOOKUP is it's a little bit clunky, it's a little bit restrictive. It's not very flexible.

You have to hard type or hard code this column number in.

So we're going to sort of try and make it a little bit more flexible by adding a MATCH function. And we're going to use a MATCH function to identify which particular column we want.

Now, in this case, we're going to start with the price column.

So I'll show you how MATCH works. Equals MATCH, and MATCH basically works by saying, "I want to find price." And where do I want to find price? I want to find price within this set of row headings, which are the headings for what will be my VLOOKUP table.

And it also gives me a little bit of flexibility at the end.

Again, a little bit more than VLOOKUP.

It can be an exact, less than, or greater than. I'm going to go with zero.

I want an exact match. I hit Return, and it comes up with two. Now, if I change that to Revenue, you can see it's now saying it's four. It's the fourth item in the list.

Okay, I'm going to go back to Price, and now I can use that MATCH function to drive my VLOOKUP. So I say equals VLOOKUP, and I want to find A134, just as before. I want to highlight my entire table like this.

But then, rather than just hard coding two or four for the relevant column, what I do is I point towards that MATCH function.

I hit comma, and then I want an exact match again, so I'm just going to put zero, which is equivalent to false, an exact match. I hit Return, and it comes up with 3.5. And again, let's just check that it works.

I change the product to X212, and it comes up with 4.8.

But the clever thing is I can now say I want Revenue.

That MATCH function says that's the fourth column in the list, and this VLOOKUP is now looking for the Revenue column rather than the Price column. So we're starting to introduce a little bit of flexibility.

Okay, let's go a little bit more flexible again.

And this, I guess, was where when I started using Excel in anger, this, I think is where you could really see the difference between people that were sort of novices at Excel and people that were real power users using INDEX and MATCH in preference to VLOOKUP. VLOOKUP, as I said, a little bit restrictive, whereas INDEX and MATCH are a bit more powerful, bit more flexible. And so we're not going to do VLOOKUP here.

We're going to create something that looks and works like VLOOKUP, but using a combination of INDEX and MATCH. And what INDEX does, INDEX basically refers to an array, to a block of data, and lets you pick out of that array the intersection of one column and one row. So we're going to start with two MATCH functions.

I'm going to say, first of all, MATCH, and I want to match the A134. And where is the A134? I'm going to highlight all of these elements here. I'm basically going to highlight the left-hand column, and I hit zero again for an exact match, and it says that A134 is the second item down in this list.

Price, exactly the same as in the previous example.

I'm going to say equals MATCH.

I want to find Price, and where do I want to find it? I want to find it in the column headings this time.

Again, with a zero at the end for an exact match, and that, again, is the second item in the list.

So now we wrap these inside an INDEX function.

We use the results of these two MATCH functions to drive the INDEX function.

So we say equals INDEX An index basically points to an array.

So first of all, we define the array. There it is.

And then you can see that it's saying I want next the row number and the column number. So the row number is basically that match function there.

It's the second row in the list, that's where we match to A134, and the column number is that second match function, the price column, it's the second column in the array.

I wrap it up with brackets around the end, and I get the same 3.5. But now if I change, for instance, revenue, it says it's the fourth column, and my index function comes back with the revenue of A134.

And exactly the same if I change it to X212, and I'll get the correct, the revenue for that.

So we've effectively created a match, so we've created a VLOOKUP function using a combination of match and index.

But it's now much more flexible. We can drive it with a range of different things.

Okay. Next, we're going to use an offset.

And what I'm going to do with this, as I could have done with the previous index, I'm going to wrap up the match function inside the offset function.

So what's the difference between offset and index? Well, index points at an array. You have to define the array that contains all of your data, whereas offset just points to one cell, and then you can drive the movements away from that with counters. You can either go a number of steps right or left, or a number of steps down or up. And again, we're going to use a match function to do that. So we're going to say equals offset, and we're going to say our starting cell is this one, C4 T5. And it's often the case that what we do is we point to one cell that's just one cell outside our array of data, and that's what we've got here.

So it's often one cell above or one cell to the left, or in this case above and to the left of our starting data.

And that's because when we drive it with a match function, we're going to say we want the first product in the first row, and that's going to jump down one.

And we want the first column, a price, and that's going to jump to the right one column. So we now have a match function, and it tells us that we want the row. We want the row here.

So we're going to use a match function.

We're going to point it to the A134, and we're going to say match A134 to this array here, and we want an exact match with a zero at the end.

And then in terms of the columns, again, we want a match function. But this time, we want the price, and we need to find that within our headings there. Again, exact match at the end.

I hit Return, and I get exactly the same answer. And again, let's just try X212, and it comes back with the correct one, and it'd do the same if I change price to revenue. Okay. So offsets and match, and index and match, all are very powerful. You can either wrap the match functions inside the function, or alternatively, have them outside as we did before.

Next, we're going to use XLOOKUP, and XLOOKUP is the kind of the latest all-singing all-dancing, the latest version of VLOOKUP.

And what XLOOKUP does is it basically introduces some flexibility.

It tries to solve some of the problems, or the limitations, the lack of flexibility that VLOOKUP had.

Be aware, XLOOKUP is only in the most recent versions of Excel, so if you're using a version of Excel from three or four years ago, it may well not have XLOOKUP. So here goes.

We'll start with our function, XLOOKUP, open brackets, and it gives me that hint again. So what am I looking for? Just the same as VLOOKUP. I'm looking for A134.

Now, here's the difference. Basically, it asks me, where do you want to find that particular item? And the answer is in this left-hand column.

And I don't have to highlight the entire array, and that's because XLOOKUP enables me to have the lookup array and the return array in very different places. It could be to the left, it could be to the right, it could even be on another sheet. Okay. So we're now going to point to the return array, which has to be the same shape as the lookup array, and it happens to be just one on the right.

You can also see at the end, I've got lots of other options.

What happens if it's not found? What's your match mode and your search mode? Lots and lots of flexibility with XLOOKUP.

I hit bracket to finish the function, and I get 3.5, just as before, and again, try X212.

It works. Now, here's the fun bit. Here's the really sort of complex bit.

So what we're going to do now is we're going to look up a nested XLOOKUP.

And so follow me on this. I'm going to basically do it in two stages, two sort of bites.

So the first one I'm going to say is equals XLOOKUP, and I'm going to say I want to find the price column.

Okay? So I'm going to point to price, just as I did before, and oops, I need a comma at the end. And then I'm going to say, where do you want to find that? In this row up here.

And again, XLOOKUP doesn't mind. It deals with rows and columns.

You don't have to have a different version like VLOOKUP and HLOOKUP.

So it says, "Where do you want to find?" I want to find price in this set of headings. And then it says, "What do you want to bring back when you've found price?" And rather than just say one line like this, I'm going to highlight the entire block of data, and look what happens. I hit Return, and it basically comes up with all of the price column.

And so what it's done is, you can see in the very top, I've got the formula in black Just as I had before, and it's coming up with the 3.5.

But if I go down, it's basically put all of the formulas in pale gray, and it's put a box around it.

And what it's actually done is it's spilled over.

It's said, "You're referring to a whole array. No problem at all.

I'm going to bring all of that array back, all of that column, I'm going to bring back." And it's interesting because it treats it as a single item.

So if I try and get rid of the 50, if I hit delete, it won't do it. Because all of that column, all of those five numbers are treated as one item.

Now, what I'm going to do now is I'm going to say that's the source going into another XLOOKUP function.

So I'm going to start, I'm going to use my mouse.

It's easier to do it with my mouse. I'm going to write another XLOOKUP.

And now I'm going to look for the price.

So I'm going to look for, oops, where's my mouse? I'm going to look for A134, as I did before, and I'm going to look for it within that block of data, those product IDs on the left-hand side.

And then it says, and where is your return array? But I don't need to type anything because the return array is coming from that first XLOOKUP that I've already produced, that column of data that's already been extracted. We're looking for the price.

I now want to go to the end, put another bracket around it to close off the function, and I'm hoping, yes, it does, it works.

It comes back with a 3.5. Let's just check it works.

X212, it does. Revenue, it does. So what's basically happening here is the first XLOOKUP function that we did, the first lookup function we created, is coming back with a whole column of data, and that is providing the source for the second XLOOKUP function, which is then looking for just the correct line, the correct row from that column that it's come back with. So really very sophisticated.

Shows you just how clever XLOOKUP is. Okay.

We've got a couple more things to do.

We've got an indirect function, and we also have some transposition. So I'm going to do the transposition first, and then we'll finish off with indirect. So if we dive to the right-hand side, we've got transposition, and we've got 10 minutes, so we're okay for time.

So I'm going to go to this transposing section, and I'm not going to do all of this, just going to do the first couple of examples.

But imagine here, what you've got is you have basically got some information that's been dumped into a spreadsheet, and it's great, it's all the information, but it's the wrong way around.

So in this case, what I've got is I've got countries on the left-hand side, and I've got products, fruits, on the top.

And I want them the other way around.

Now, of course, I could sort of retype it, but that would be pretty manual and susceptible to errors and so on. So how can I get around this? How can I transpose the data? Well, if I only need to do this once, it's just a one-off exercise, I can just do a copy and paste special.

So what I'm going to do is I'm going to highlight all of the block of data.

I'm going to hit copy, which is Control + C, and you can see it puts a little dashed line around it. I'm going to go to underneath where I need to transpose it, and I'm going to hit paste special.

Now, I could use paste special from my menu in the top left-hand corner, or I can use the keyboard shortcut, Control + Alt + V. Control + Alt + V is paste special.

And there's loads of great things in paste special, pasting values, pasting formats, using operations to multiply by things by minus one or 1,000 and so on. But I'm going to click this one here, transpose.

Click on transpose, click OK, and it does exactly that.

It swaps them all around. And you can see I've got 3.6, 4.1 along the top row. They used to be going down the first column. I've got 17.3 in the bottom right.

So it looks like it's done a good job. It's copied everything through.

Now, of course, it has just pasted numbers.

It's pasted figures. It's not a link to the original data.

So if the original data changed, if I made that 30, the one underneath doesn't change. Okay? What are the alternatives? Well, a great alternative is the transpose function. So I'm going to hit equals transpose.

And it asks me to put in an array, and that's what I'm going to do here.

So I'm going to highlight this array, the whole array.

I'm going to wrap it with a bracket.

I'm going to hit equals, and it does it. It works.

And you can see it comes up with the same answers as the previous version.

And there's an interesting point.

If I go into the data, you can see exactly the same thing happening as we had with the XLOOKUP function. It's basically just put the formula in the top left-hand side and spilled all of the data to the right and underneath. It's all one block of data.

And again, if I try and hit delete on one of the cells in the middle, it doesn't do it because it's treating the entire thing as effectively one object.

The benefit of doing this is that if I change this top left-hand number, say, to 35, you can see that down here, that one changes as well. So there's a live link between the data.

It is just effectively copying and it is just putting a link into the new data in the right format.

And of course, we can link to this, we can extract data from this, but again, we can't just take one of the items out.

Now I'm just going to mention the one that comes underneath, OFFSET row and column I'm not going to do this.

We'll run out of time if I start going through this.

It's reasonably labor-intensive to do this, but do have a look at this in the solution. What it does is it uses that offset function we've already used, the one that basically says start at one cell and then move to the right or move down.

Well, what we do is we use that and we drive it with a row and a column counter to gradually establish these links. But effectively, as we copy to the right, it's pointing to cells that move downwards and vice versa. Really quite sort of logically sort of elegant, but does take a little while to get your head around. Okay.

And we've got about five minutes left, so just enough time to do our last exercise, and we're going to move to the indirect spreadsheet. So if you can open up the indirect spreadsheet and it basically says indirect workout empty. So if you can open up that one, then you'll have the same things that we're going to do.

So I'm going to go to workout and I just need to make sure, just remind myself exactly which one of these I was going to look at. And I think it's workout three.

Yes, it's workout three. So I just want to have a quick little look at workout three. And just before I do the workout, I'm just going to remind you of how Excel references a cell that's on another tab. So this wants me to look at the next few tabs. You can see I've got Germany, Indonesia and South Korea, and it wants me to pull out some data from each of those tabs. So if I hit an equals and I did Control + Page down to go and find the capital city of Germany, you can see it's here.

It's in Germany, C4. Hit Return, and I'll put the reference there. So what's the reference? It's basically saying refer to the sheet name first, which in this case is Germany, then an exclamation mark that separates the sheet name from the actual cell reference, and then the cell reference C4. Now what indirect does is it enables you to build up a reference in text, and then when you put that inside an indirect function, the indirect function evaluates it. It basically says, "What is this reference?" Ah, in this case, it's going to be Germany!C4.

So I'm going to take that bit of text and I'm going to go and find the Germany tab, cell C4, and bring back whatever's the contents of that.

So the key with the indirect is it's basically wrapping up a piece of text that contains a reference. So I'm going to write indirect, and what I want to start with is Germany, the sheet name. Now, you'll see if I copy this down, I'm going to end up moving down to C5 and C6.

So I want to lock the sheet name, but I just want to lock the row. So I hit F4, and I want a dollar in front of the 38, row 38. And then what I want after the Germany is I want an exclamation mark.

And the way that we glue pieces of text together is we use an ampersand, this and symbol, and that's going to say Germany &. And then what comes next? Exclamation mark surrounded in quotes.

And then another and because I want to glue the next piece of reference in, and that is the contents of this cell, which is C5.

Let's see if this works.

And it does. It comes back with Berlin.

Now if I've got this right, I should be able to just copy this down and it'll bring back all of the data from Germany. Control + D, and it does. It brings everything back. And now the really clever thing is if I copy this to the right, it should basically bring back all of the information.

And this is pretty clever because it's basically pointing at three different tabs.

So you can see how flexible this is.

You could basically bolt on extra tabs of information.

If you were a retailer with information for every single one of your retail outlets, you just keep on bolting on every new shop as a new tab, and the indirect function will enable you to navigate throughout that very big spreadsheet. Just going to show you one more example of this.

We've got a couple of minutes left, and it is, let me just scroll down, and it's this one here, workout six. So let's just dive down to workout six.

So in workout six we've got a number of spreadsheets, Account X1, Account X2, and we want the fund value, and it tells us that the fund value is in C7. Let's just have a quick little look.

Account X1, I've got fund value, which is in this row here, in row seven, and it starts in column C. So let's go back to our workout, and let's show you how we can use the indirect formula again. So we say equals indirect, open brackets, and we want that Account X1.

That's going to be the sheet name. I want to lock that column so that as I copy to the right, it doesn't move.

So I hit F4, and I lock C, so it says $C. And then I need an exclamation mark in quotes bolted together or glued on with that ampersand.

And then I need that C7 just above. And again, what I actually need here is I need to lock the row number in here. So I do F4 until it locks in the 93. I hit Return and I get the 110, which is the correct answer for Account X1. I copy that down, Control + D, and it's now pointing at all of the different accounts.

Copy to the right, and again, you can see how elegantly it's populated this using that indirect function with really very little work once I've got this set up. Okay, just one word of warning with indirect.

It's incredibly powerful, but it requires scrupulous consistency on your tabs. Every single tab has to have exactly the same layout with the same thing in the same place. Otherwise, the indirect function will just be pointing at empty cells or the wrong information. So, very useful if you can kind of ensure that's the case, ensure that all of your base information on all of those different tabs is collected in a totally consistent way. Then indirect's a great way of navigating around that.

Okay, exactly 30 minutes past, so that's exactly 30 minutes of me.

That's my time up. So I hope that's been useful.

Do download all of those spreadsheets, download the empty versions and the full versions, and hope to see you on another one of these very soon. Okay. Thanks very much.

That's my time up. Okay. Bye bye.