 Hello everyone and welcome back to another video in this tutorial We're going to be showing you a top trick using the offset function and how you can use that to retrieve the last Value in the row So to demonstrate this we'll first look at the example data We've got to help explain what we're going to be using the function for to achieve So you can see and excuse us for the boring test one and two and then the fines But hopefully this will demonstrate obviously how it work, and then you can use it in a more realistic situation So what we've got is we've got rows from six to twelve and in those you can see that the value that we want to find And that varies by find one two three four five six and seven says seven that we want to find and what we want to do Is we want to retrieve this last value from each row? But as you'll notice obviously that value appears in different locations for each of those rows So it wouldn't be we wouldn't be able to just say okay equals every value in column D Right and it's like we have to be able to dynamically identify where that value sits So what we can do is use a function called offset and before we go into the function a real beneficial way of using this And this is where we actually got the idea for this tutorial is we're working with hierarchies And obviously each person who works in the organization might have a different number of managers to the next person So we always want to try and obviously you accumulate those up to find out who the top level manager is as such So this is where we'd use that scenario to try and work out find that top manager From obviously varying arrays of people with different numbers of managers and we hope that hasn't now made a confusion But hopefully this will help demonstrate a solution to if you come across this this problem in your work So what offset does allows us to select our starting point and then to offset So obviously increase the range by either a set number of rows or set number of columns And to be honest actually probably easy enough just to jump into the function and show you how it works With the prompts that Excel gives us so the first thing you do is go equals and enter our formula or function Sorry of offset and you can see the various prompts that we have in here So we won't touch on the height and width. So that's the last ones we have there We don't need to worry about those the only three we're going to first look at is reference rows and columns So what the first thing we need to do is say, okay, well, where's our reference? Where is our starting point? So for us our starting point is going to be the first column So all we need to do is select that and then when we say offset It's okay. How many rows or columns do we want to offset from that starting position? So if we either go by two rows, then we would go we'd jump to rows to take us down to row eight If we're to offset by two columns, then obviously we go across to so we'd end up in column D So having set our reference our starting point we can do comma and move on to rows Rows for us in this example here We're not going to be using so we don't need this but obviously you could do the reverse if you were trying to look for last row Rather than column in our scenario here So all we need to do is just do a comma and then we can get into the rows Remove from the row section and then go into columns and you'll just notice just left it blank You can see the two commas that we've got there back to back So what we're going to do now is firstly, we know we want to offset this first one by two columns So all we need to do it really in here is we keep type the number to close our brackets hit enter And you'll see that Excel is going to return to us the value or the So we say the cell reference and its value that is offset by two columns from our starting position in column B So that's where we end up into column D But obviously we don't know what that is for all these other ones here So if you to drag this formula down you can see that we're always going to just pull in whatever is in column D So we can see what test 3 we've got blank here in row 8 and so on and so forth So it's not going to work for us So we need a dynamic solution to understand how many values we need to offset by So all we need to do to that is rather than put the value of two We're going to use the count function to count how many obviously columns are being used based on the number of values That there are so once we've reviewed number two as we've got an example all we need to do now is replace that with counter So c o u n t a and then open our brackets and then we can extend this to the width of obviously the data that we have So we've got four columns here. So we just extend it to that Close our brackets at the end there and then what we'll need to do is we just need to include a Minus one and the reason for doing this is what we have at the moment here What's going to happen is our function is going to count obviously all these values in this row So in this example here, it will count three. Well, we don't want to offset three columns from our starting position of column B We only want to do the two so that's why we bring the minus one in there so that it goes down to to the correct number So you just put minus one in here close our brackets and then when we hit enter you can see obviously it's found our desired Value which is find one so the last value or the last column that has got a value populated into it If we now copy this formula all the way down You can see that it's correctly updated for all those other values as well And that's because obviously it's updating based on each of the different columns and all we can do here So if we enter our counter function, it should help to demonstrate what we've done. So counter all of those columns Minus one and we can then pull this one down here. So it's now pull that down So for the first one it'll obviously start in column B and it'll be offset by two So I go to places to be in column three and this other second example We've got in row seven. This is gonna be offset by now three So I'll start in location of column B offset by three We give us one two three to end up in column E. So there you go We've hope you found that that top tip really useful We hope obviously more importantly it made sense and we'll just click in here Just so obviously you can just see that function once again as we close off If you have any questions at all, please just drop us a comment below this video or Alternatively links in the description to this video for our website Facebook and Instagram So any questions you can also reach out to us there and we'll get back to you as soon as we can If you did enjoy the video, please do give it a big like it be greatly appreciated by us And obviously it really helps out the channel and lastly if you haven't already please do subscribe to the channel hit that Bell notification button so you're notified of all our future videos and That'd be greatly appreciated as well. So thank you very much and we shall see you in the next video