In this session we're gonna have a look at a couple of Excel functions that can help us with identifying how we can actually extract data from a larger data set.

We're gonna have a look at some pretty small data sets, but the concepts apply more broadly to the large data sets as well.

So hopefully that's gonna be something that we can transfer and apply across and really look at the range of different ways that we can actually, um, access this.

Uh, content should be able to see the Excel file I've got on the screen here.

Uh, we'll see how we go with time as to whether we get through everything.

But what we'll have a look at first of all is looking up a data point from a larger data set and we can use some lookups for that.

We'll then think about how the indirect function works as well.

So that's where the multiple sheets heading comes in.

And if we get time, we want to get to all wrapped up in the half an hour session that we've got.

We'll have a look at transposing data, but probably the first I think are the more important of the two.

So, uh, probably worth starting off there.

So we'll dive in with that.

So if you can go to the lookups tab.

We're starting off here with some really, really simple data points, uh, but just trying to show you the range of different ways in which you can identify some data from a larger dataset.

So, um, this will apply if you've got thousands of data points.

We've only got a very small number to begin with.

I'm just gonna zoom in a little bit, so that's a bit easier for you to see.

Um, but we've only got a small number of data points here, but it works equally well across large data points.

Hopefully having for the small data points means that you can see what's going on.

So we're gonna start off with the, a fundamental Excel function that's relatively straightforward and easy to get our heads around.

Uh, and then what we'll do is we'll add complexity and add alternatives in as we go through and try to give you an idea about what alternatives, what the alternatives can do for us in terms of their benefit.

Um, so uh, starting with the V lookup function where it just says V lookup numeric, what we're trying to identify here is what your tax rate should be based on your income.

And we've got a range of different possible tax rates range based on different income levels.

And what we've got is somebody's income level here and we wanna see what tax rate they should be, should be subject to and what we need to use here. What will help us out with this is the V lookup function. If you just type VL should be able to hit tab to select that function.

And then, uh, it is a somewhat complex function but if we follow along with what's going on in the gray box up here at the top where the uh, row left the column that wrote column letters are, then that should be able to help us out here. What we're looking for is while this function is telling us, well what are you looking for? Well we're looking for 25,000.

And then it says, well where do you want me to look? And the way the V lookup function works is that you can only search the set or only be looking for something in the far left hand edge.

So you can only look in that far left hand edge for the thing that we're looking for here income.

So that's gonna work. And so we can hit column, we've gotta select all of our data and then hit comma, it says column index number.

And that's basically saying, well if I go down and I find the 25,000 I can go across to any other column and well which columns do you want me to return? And we've just gotta count the number of columns across.

We want to go within the selected area and here we want the tax rate, which is the, in the second column.

So just the number two for now.

And if we hit enter on that, then what that will give us is the 30% tax rate that we're subject to.

If we earn 25,000, what it's doing is it's starting at the top and it goes, well I'm looking for 25,000, haven't got there yet at zero and still at 10,000.

I haven't got 25,000 yet.

Uh, but if I keep going down too far 30,000, I've gone too far.

So it'll stop here and return me the value in the second column of the selected area array.

That is 30%. And if we had a salary and if we had a salary of 75,000, then it's going down past the 30 to the a hundred, that's too far, we go back and save 34%.

Okay? So that all works well and good for us if we're looking for numbers.

But unfortunately in a finance context we are rarely looking for numbers on the left hand edge of our uh, VLOOKUP function.

What we're typically looking for is text.

Now here we've got an example of product IDs, but in a finance context it could be trade IDs, it could be a client identifier that we're looking for.

It could be the name of a fund. If we're looking at aggregating fund performance.

So um, often we're looking for text on the left hand edge here and the VLOOKUP function starts to get into a bit of trouble here. It doesn't quite work perfectly.

So let's just see how this is gonna work for us and just to prove the problem we've got, if we go and again go for the EV lookup, okay? And we want to find not a number anymore, but now this product ID a 1 34 to begin with at least well again look up value what we're looking for, table array, where do you want me to go and look or to go and select that whole set of data, okay? And then if I want to return the value in the second column, the price of these products, then I can just do that. And it seems like it works for the first product.

That doesn't seem like it's too much of a problem for us.

But the problem comes if we look at let's say C 4 1 3, if we look for C 4 1 3, we should get a price of two. Second column here should be two.

But it's given us for some reason 4.2 which isn't product C 4 1 3.

And there's a good reason for this.

XL isn't like guessing what it's doing is it's going down the left hand column like it did in the previous tax rate example.

And it's saying okay, well I'm looking for C 4 1 3, let's go down until I find C.

Well a's early in the alphabet, BS early in the alphabet X is too far.

So previous value, let's go for that one.

Okay, that's what it's giving us 4.2.

But that's not correct for us here.

'cause what I want is to find the C four 13. Absolutely.

Exactly. Not just kind of close.

So what we can do if you go back into the function F two to go back into the function and at the end you'll see if we hit comma after the number two, what it'll show us is that we then have this square bracket optional element, uh, any Excel function here that has a square bracket around it is an optional element where we can either look for this approximate match that true value is the default position or if we're looking for a value exactly we can hit down arrow and then tab to select false.

Now you can type zero in here, Excel reads zero as false outlook equally well.

So the default function here is an approximate match, but we wanna look for C 4 1 3 exactly.

And by making that change we do now get the value of five that we want.

Okay, the price of C 4 1 3 is five and if I go B 2, 2, 1, 1, then that gives me 4.2, which is fantastic.

Okay? So it looks like it's working, no problem at all.

Great. We do have a bit of another problem though.

And the other problem we've got is that generally within Excel functions we don't like hard coding numbers.

That's a general premise.

It's not a great idea to type numbers in for the vast majority of the time because actually we're losing flexibility here.

What we want is a model that is flexible to our inputs and where we haven't gotta go into the formulas and change the formulas once we've done the building of the Excel calculations.

So what we can do or what we'd like to have is not just a number here, I mean it works, but really we'd like more flexibility so that if we go down to the next example, if we weren't just looking for a product ID where there was just a price, but maybe there was other outputs we were looking for maybe price, but maybe quantity sold, maybe revenue as well for this particular product id, well we're not gonna be able to do that if we just hard coding that number two.

So what we're gonna need to do is to somehow convert the word here for the second factor we're looking for.

We're still looking for a product id, but the second thing we're looking for is the column price or quant sold or revenue.

Somehow I want to change that word price into a number that corresponds to the column of the selected area, the array that we're looking to return.

Okay? And the way that we do that, maybe it's not a massive surprise given that it's up here on the top left hand side, but we do that using a match function.

What the match function does is it returns a number that reflects the location of an item within a select area range.

Okay? So what we do for the match function is again, we're looking for a certain value, the location if you like, of a certain item in a range.

So we've gotta tell it, well what are you looking for again? Just like the vlookup, what are you looking for? Well I'm looking for the word price and where am I looking for it? Well I wanna look for it in a dataset where it gives me the number two where it's the second location and then quantity sold is the third location and revenue's the fourth.

Well if I go and select all of those column headers in columns C to F, then price is the second location reading left to right in this selected area.

And if we hit comma again, we've got the square brackets there.

Uh, okay, so the match function does also have greater than but also less than uh or an exact match.

And it is the exact match that we want.

So down to the exact match hit tab and that should now look for price.

Exactly. Okay.

And you can see that what that gives me is the word price.

The four column headers if you like.

I've selected price is the second location.

So the match function gives me the number two, which is great because I want the number two to go into my V lookup function.

Okay? So rather than typing in the number two, what we could do instead is build the VLOOKUP where we have the match function in there instead.

So I've just seen a comment where we don't have the uh, excel function.

Let, don't have the file, let me grab the file for you so I can put it in the comments again.

Hopefully that should be in there now for you.

Okay, good. Okay.

So what we are doing with this VLOOKUP function, let's just uh, go back and have a look at this Again, what we're doing for the VLOOKUP function is we are trying to identify what this would uh, look like in terms of the uh, match function being used instead of hard coding or typing in a number.

Okay? So same set of circumstances as before.

First thing we're looking for is, well what we looking for? Well in the left hand edge that lookup value is gonna be still the product id.

Okay? And then comma next bit in bold, where do you want to look? Well still the same range of data as before. Okay? So no change there. But now instead of typing in a number for this column that we want, if we find a 1 34 in the left hand edge, we want not a number, but we want the match function to give us a number based on what we're looking for.

While that's the column that we're looking for at the moment it says price in C 31.

That again needs to be the second location.

Just gotta select all four of the column headers and again, zero for an exact match, close off the match function and then comma again.

Well I want again a exact match for the VLOOKUP function and close that again.

Okay. So what we now should be able to see is that the match function that we use to get the number two is effectively embedded within our VLOOKUP function.

So rather than just typing a number in for the column that we want back, we're now using that match function to give us the number of the column that we're looking for.

Okay, great. That hopefully all makes sense but let's just test it to make sure it's actually working.

If I go for C 4 1 3 and also revenue, does that actually give us what we're looking for? Well we can see that as I type revenue, the match function gives me the number four, which is the fourth column here. So that looks like it's gonna be working for us.

And if we are looking for C 4 1 3 and revenue, it is 1190, which is precisely the value I'm getting returned down here in C 32.

So this enables us to go and find a particular value within a here pretty small set of data, but you could apply this in a much larger set of data as well.

Okay, one other thing you do need to be a little bit careful with here though is that if you type in anything slightly wrong here, so if I put a space in at the end by mistake of C 4 1 3 and it doesn't work, I mean it looks like it's working, but up in the formula bar you can see there's a space at the end.

Since we're looking for an exact match, it only looks for C 4 1 3 exactly not C one four. The space at the end of it that isn't close enough for it.

Okay? So what we might want to do is limits what can go into these input cells using the data validation, which is ALT A VV and lemme just come outta that for a second.

So data validation, lemme just put up at the top of the screen for you.

The shortcut was alt A VV.

If we do that, what we can do is limit what we're actually able to put into this cell.

So if we hit alt A VV for data validation, if you hit tab at the moment, any values allowed in that cell.

But if I go down to a list and then hit tab again, I can use my arrow keys to go and select just the here four product IDs, sorry, five product IDs.

And you'll see that as I do that there is then a dropdown list back down arrow on the bottom right hand side and if you hit hit alt and the down arrow, then it limits me in terms of what I'm able to actually type in that cell. If I try and put a space in at the end, then it says it doesn't work.

So I get a warning message saying that that doesn't work.

Uh, and as a result I'm not able to input that value. Okay? So that can just help you to uh, aid users in actually not messing up the be lookup function that you've created.

Okay? There are two other functions that fundamentally do the same thing, but they give us more flexibility.

These next two functions we're gonna look at the index function and the offset function. Essentially do the same thing.

They give us, both of them give us more flexibility, uh, than the VLOOKUP function, okay? The limitation of the VLOOKUP function is you can only search for, you can only look up stuff that is in the left hand edge of this table, okay? So you can't search for maybe price.

If you know the price of a product, you can't search for what its product ID is because price is not the left hand edge of this table, okay? Whereas you could do that with the index and match function, there's much more flexibility afforded to us here, okay? And we're gonna start with the index function.

We're gonna use match to give us the numbers still as before and it is a bit more complicated here, but fundamentally exactly the same.

Mechanically if we type in index, okay, then we're using the top range here, the top row of what we're looking to input, which is firstly an array. That's just a selective area where we want Excel to go and look for us.

So I'm gonna select everything shift and control and down and right to select all of that table.

So that's where I want Excel to be working.

I only want Excel to look in this limited range of data, okay? What the index function then needs is a number that corresponds to the row of this selected area that we're interested in.

Well the row that I'm interested in is the row that corresponds to my product id, which here's gonna be the second row.

So we're gonna use the match function again to turn a 1 34 into a number and it needs to be a number two.

So I need a 1 34 to be the number two.

Well I can just go and select the left hand column data points.

A 1 34 is the second row down, okay? And then comma again exact match, zero, close off the match function for the number of rows that we're interested in.

And then comma again then says, well how many columns across do you want me to go in this data set? So at the moment the match function is saying go to the second row down of the selected area.

And then what I need to tell Excel now for this index function is how many columns to the right I've gotta go.

And again, we're gonna use the max function just like we did before for price, where price is the second column, again selecting the data in row 34 and again zero for the exact match.

And then we can just close off the index function and you can see what that's doing for us is giving us the same value.

And if we change this to C 4 1 3 and revenue, it does give us that 1190 is before.

So the mechanics are no different.

But we are having to tell Excel all about which road to go and look at or where to go and find information on which road to have a look at.

We could have set the space up on price, we don't need to use column C.

You could use column D or column E or column F for that first match function to give you the number of rows that we need to go down in the selected area.

Okay? So yeah, it's a bit more involved. There's two match functions embedded within the one index function, but hopefully gives us more flexibility.

Okay? The offset function does exactly the same thing just in a slightly different way and there are some technical differences, but let's just have a look at it works first, but fundamentally you can use an index function or an offset function to achieve the same outputs and it basically works the same way.

But that the offset function doesn't require us to select the area, the array where we're gonna have to look Excel instead for the offset function just asks for a starting point.

Now, from that starting point, we then need to give Excel a number for how many rows down to move and how many columns to the right to move.

But that's about it. Okay? So same as before, rather than the index function needing the whole array C 34 to F 39, the offset function just needs a starting point, which here is C 45.

Okay? We then need to tell it how many rows down we've gotta go.

Again, we want to go down from here to a 1 34.

So a match function and can be a little bit careful here.

I wanna move a number of rows away from my starting point of C 45.

Well I'm looking for a 1 34 and I can see that it is one row below my starting point of C 45, which means that I'm starting by selecting and there's match function C 46 to C 50.

I'm only selecting the five product IDs to give me one or two or three or four or five rows down from my starting point.

And again, zero for that exact match.

And then comma, how many columns to the right are we moving from our starting point of C 45? So match function, again looking for the word price, but where price is one column to the right, which means I only wanna select the three return column values essentially of price, quantity sold in revenue and again zero for the exact match.

Okay, so and a final comma as well just to round the uh, offset function off. That's what I missed there. But then you can see that there's a lot of similarity between the offset and index formulas in that we've got for the index function, a selected area for the offset, a starting point, but from then basically the same.

But where we're selecting for the match functions, one less data starting point.

We've got more in the match functions here.

'cause in the match function you are looking for the row and column location in our selected area.

But these match functions within the offset.

We are looking for the data points away from our starting point columns, uh, to the right and rows down from the starting point.

But again, you can have the starting cell, the reference there wherever you like.

You can set your offs, your match functions based on whatever you like is your starting point. Just much more flexibility here. Okay, good.

There is also an X lookup function.

Now the X lookup function is excel trying to help us out a little bit and move away from the V lookup only be able to search on the left hand edge.

You can search whatever you want here with this V lookup function, sorry, with the X lookup function.

'cause the X lookup function says, okay, what would you want me to look for again, let's just go back to the A 1 35.

It says, where do you want me to look for it? This doesn't have to be in the left hand edge of the total area.

Well I want you to look for it in these five rows.

And then it says, well if I find it, what do you want me to return? And we've gotta go and separately select the price output.

Okay? So just means that we don't have to search on the left hand edge, okay? Still gives us the right number.

Still's gonna work totally fine for us in terms of return on those values, but uh, it doesn't mean that we have to look on the left hand edge.

I think that if you know how the offset or index function works, you don't need the X lookup.

I mean it's a bit more flexible than the V lookup, but if you've got index and offset, then doesn't really do much extra for you in most instances.

Okay? So hopefully that's useful in terms of those, uh, functionalities in terms of the differences between index and offset.

Index uses slightly less memory because you're only lasting asking Excel to look within a limited range of data points in the file.

Whereas for the offset, Excel might have to go anywhere 'cause you're only just giving it a starting point.

So slightly less excel memory for the index function.

But the offset function has the flexibility that if you add more data at the bottom, it'll just get picked up straight away with it's the index function it might not do.

Okay? So for the offset, if you're adding extra data later, then it'll always be picked up, no problem at all.

Okay, just to finish things off, we're gonna have a look at the indirect function.

So if we just jump to the multiple sheets tab for a sec for a uh, second.

Okay.

Uh, sorry. Uh, I've only again, just in your comment, the uh, hopefully I know there are some limitations on, uh, what some companies allow you to see.

So if you can't see the Excel file in the chat, it's uh, typically a, a function of the, uh, restrictions that your company might have placed over Zoom.

If you do, uh, drop us an email afterwards or we can get those Excel files out to you afterwards when we have access to your, uh, email addresses. Apologies for that. Okay.

So for the uh, index, the indirect function, the indirect function is something that often, uh, catches people out.

I'm just gonna change this a little bit just to make it a little bit, um, more, uh, quick to explain how the indirect function works.

Okay, so the way in which we might commonly see the indirect function being used.

So that's what we're thinking about here is in creating summary tables.

Okay? Now if we look at the north tab, the east tab and the west tab, you can see that the uh, alignment, the locations where the quantity sold of each of these product sits is in the same cells in each of these taps.

So if we've got a template for each underlying data point and we wanna summarize them all on a um, main uh, kind of dashboard, maybe then we could here equals go to the file, grab the data point that we're looking for, that's in column C, not D, and you could copy that down control D.

But then for the next tab for east, you've gotta go to the east tab to actually go and grab the numbers here again.

And I mean it's not the end of the world, but if you had maybe monthly data points in separate tabs for the profitability of different regions and you're looking at analyzing data for the last year, two years, three years, do you really wanna do this 36 times? It's gonna get really painful after a while of doing that, okay? But what the indirect function can do for us is it can enable us to more quickly actually create these summary tabs, okay? For that one cell that I've highlighted, you can see that the formula is north and then exclamation point, which is the Excel notation for the name of the tab or the worksheet that we're looking in.

And then C seven, which is the row and uh, column letter and number.

What we want to be able to do is to create that cell reference telling Excel where to go and look, but without having to go to each tab individually.

And that's where the indirect function comes in really useful.

Uh, again, I'm just gonna change these just to demonstrate how this is gonna work for us super quickly given the time we've got left.

Okay? And let's just make this a bit wider.

And then I wanna put the cells that we're looking for.

So it's gonna be C seven and let me just make that visible.

And then it's gonna go down to C 8, 9, 10, and 11.

So these are the cells on the separate tabs where the numbers for the units of cells and the total are actually located.

What I wanna do with the indirect function is create this cell reference north exclamation point C seven using the num, the numbers of the values that I've got in these cells here.

So what I'm gonna do is use the indirect function.

And the indirect function is just a way of compiling a cell reference, potentially using the components of other cells.

So I wanna get the word north in there, but I want north to change as I copy to the right and that's good because in row 20 I've got the words north and east and west.

So I'm just gonna go to C 20.

I'm then gonna hit F four, which is how we can lock onto a cell.

But I only want to lock onto row 20.

So I've hit F four again to get rid of the dollar sign in front of C.

And now this means that wherever I copy and paste this formula to, it will always look at row 20.

But the column might change, that'll gimme the word north for this first cell reference, what I then need is the exclamation point and the way that we can link that together in Excel is the ampersand.

So ampersand, so I'm getting the word north out of C 20 and then I want an exclamation point.

But here you've gotta put that in quotation marks.

Okay, so then I've got in C 20, the word north and then the exclamation point.

Uh, and then I want the value of C seven. Well, C seven happens to sit in, sorry, AM percent again happens to sit in what I've got as a 21, right? And if I do that well when I copy this down, I want to keep looking at column A, but a row number to change.

So if I hit the dollar, if I hit F four three times, it'll put the dollar sign just in front of column A.

Let me just go and grab that cell reference so you can see what it's doing.

The indirect function is matching how we've, we've set this up.

So it's gonna look in C 20, which is the word north. Great.

It's then gonna give me an exclamation point, which I then need.

It's then gonna look in a 21, which has at the moment C seven.

Okay. So that will match it and you can see it does gimme the right number three.

Okay? The benefit of setting it up, the way that we've set it up though is that if I then just select that entire area, so shift for selecting and then copy this formula down and copy to the right, I'll always look at row 20, but C and D and E respectively, I'll always look at column A, but then row 21, 22, 23, 24, 25 respectively.

So copy down copyright, that gives me my summary table without having to go to each separate tab.

So fantastic way here. Quickly compiling all of the data from the different tabs if they're all set up following the same template without having to grab the data individually for each tab.

The other thing that is really great here is that if we wanna change the order, I can just type in west here and east in here and the indirect function will pick that up and automatically change the order in which the data is displayed as well.

So the indirect function really, really often a lifesaver in terms of compiling data from underlying tabs that follow the same template.

Okay, great stuff. Hopefully that's useful.

Um, and uh, please do get in contact if you want these Excel files.

You can practice with the lookups on the lookups two tab and we'll get the solution files out to too as well. Hopefully that's useful in understanding how we can actually manage and manipulate and find data within larger data sets.

Have a great rest of your day. Enjoy your Friday, wherever you are at in your day, uh, and hopefully see you on another one of these again soon. Thanks very much.