The real estate agents want to be able to offer their clients quick loan information, giving them an idea of what their repayments might look like. To help with this, Alex has set up a loan schedule which can be very customized. The only thing left to calculate is the higher lending charge. All the banks this agent is dealing with charge a higher lending charge when the amount borrowed is more than 80% of the property value. Not only do these charges vary across the different lending institutions. But they also increase as the loan amounts and the borrowing percentages go up. So Alex is going to need to look this up in the higher lending charge table. Let's have a quick look. They're borrowing 85% on a 212,500 pound loan from Big Bankz. If we come to the higher lending charge, HLC table, you can see here is all the information for Big Bankz. It's an 85% loan, so it would fall into this row and it's between 0 and 300,000. So it would fall into column D. So the value we're trying to get back is 0.73. To look this up is going to be quite tricky, because there are three things that we have to take into account. And sometimes when you have a really complex lookup, the OFFSET function can be a good solution. The OFFSET function basically returns a reference to a cell that is a specified number of rows and columns away from a given cell or range. What makes it quite interesting is it can return a single cell or it can return a range itself. So what does this mean? Well, if for example, I can find the first instance of that lender. And then work out that I need to get down four rows and across two columns that will help them return my value. Lets see what the offset function looks like. Clicking in cell J4, I type =OFFSET. The first argument it requires is a reference. That is the starting point. And in this case, it's going to be the first instance of Big Bankz. I'm going to click on that and then type a comma. It now wants to know the number of rows to offset by. If I put 0, I will stay in the same row, because there's no offset, no change. If I type a 1, I'll go down 1. And if I tap a minus 1, I'll go up 1. We actually want to go down four. So I'm going to put a 4 and we want to go across two columns, so I'll put a 2. You will notice that there are two other optional arguments, height and width. By default, these are 1. In other words, return a range that is one cell high and one cell wide, which is a single cell. But if we wanted to get back more values, we could. For now, we just need a single cell. So close the brackets, Enter and there's the 0.73. So the OFFSET is actually very straightforward. The tricky part comes in using calculations to identify the reference, the offset rows and the offset columns. So let's go and put all of that together. Back in the loan schedule, we're going to click in the higher charge lending percent. And we'll need to put in an IF very shortly to check that we actually are borrowing more than 80%. But for now, let's just start with the offset. The first thing we need is the reference. We need to locate the first instance in that column, which we've called HLC lenders of Big Bankz in this case. And to do that, the easiest way is just an INDEX match. We're going to put in INDEX, the array is HLC_lenders, and the row number is going to be identified by a MATCH. So we're going to put MATCH in there. And we're matching Big Bankz into the HLC_lenders. And this is an exact match, so don't forget the 0. Close the brackets for the MATCH. Close the brackets for the INDEX, that's step one. At this point, if you wanted to just check you were on the right track, you can put in a 0 OFFSET and what I'm expecting back is Big Bankz. So far, so good. Don't worry about the loan schedule breaking, we'll fix that in a moment. But we do want an offset, so let's just remove those zeros. Next thing we need to do is work out how many rows we want to go down. The way it works is it goes up in 1% increments, starting from 80.01. So to get the offset, all we need to do is take the percentage borrowing, subtract 80.01. And then round it down to get a whole number. Okay, so we will start with ROUNDDOWN and then we are going to take the percentage borrowing. Subtract 80.01 and we are going to need to round that down to no decimal places. That will give us the row offset. The last one we need is the column offset. That's actually reasonably straightforward. We just need to match the loan amount into that array at the top, which we have named loan thresholds. Again, it's a MACTCH The lookup value is the loan amount. We're looking that up in the loan thresholds, but be careful. This is not an exact match. We don't have exactly the value 212,500. We're actually looking for a less than. So this is quite important. Make sure you choose less than, close the brackets. And the step that I always forget, we actually need to account for the extra column between the borrowing percents and the lenders. So we put a +1 in there. Because if it exactly matches, we're going to have a 0. So we actually need to offset by slightly more than that. Okay, now we close the brackets for the OFFSET and hope for the best, and there we go. There's the 73% being returned by using a combination of OFFSET and some of the other useful lookup functions. In the next video, we're going to look at how we can solve some other slightly more interesting problems using the OFFSET function.
