The OFFSET MATCH function is a very powerful tool which can be used in many ways. One way is to pull in a new range based on multiple criteria which you can specify.
Let’s say we have the following:
We have a table at the bottom which lists our employees and the number of sales they had each month.
At the top is our criteria. In cell B1, we want the employee name. In B2 we want the start month of where to calculate sales. In B3, we want the end month to calculate sales.
Then we will use the OFFSET MATCH function along with the SUM function to calculate these sales in B4.
So the best way to go about this is to go through the functions one at a time.
We will start by using the OFFSET function to hilight the cells we want to add up. We would use this function based on the criteria in B1 to B3:
Since we are pulling a range, we need to start off with the SUM function.
This will sum up the range that the OFFSET formula produces. When we examine it, we will see it produces the range C7:F7. How did we get that?
Well we start at cell A6, per the function. The next part is a 1. We move down one row to A7.
Next part of the function is a 2. We move to the right 2 columns, over to C7. Then our new range starts at C7. It is 1 row high, since that is our next part of the function. And it is 4 columns wide, as that is the last part of the function.
Since it is 4 columns wide and starts at C7, then the range is C7 to F7.
So that helps explain the SUM and OFFSET functions to show how we get the result. But what if we want to make it interactive? Instead of us manually picking all these numbers for the OFFSET function, we can use the MATCH function to give us those numbers.
Let’s start with our first part, which is the 1. We want to move 1 row down. But we are basing it on the name Joe, which is what we are looking for. So we can use the Match function:
What this would do is look for B1, which is “joe” in the range of A7:A10. The 0 means it goes to the exact match. It returns a 1, since that is the first item in the range from A7 to A10.
So we can now use this formula in our OFFSET formula instead of the number 1. It will now look like this:
The next part of this formula is to see how many columns to move to the right. It is a 2 in our example.
We can use a MATCH function to replace this. We want to find the month February. We can perform the following MATCH to do this:
This looks for February in the range B6 to M6 and it returns a 2, since February is the second match.
We can now insert this MATCH function into our original to get this:
The next part is a 1. That tells us how high we want this new array. Since we will always just want it 1 column high as we are just looking up one person, we can leave it as a 1.
Finally, we need to tell Excel how wide we want this new array. We need to do this by finding the MATCH for May, which is where the new range should end and subtracting the MATCH for February.
So we would do this:
So first we are looking up B3 (May) in B6 to M6 and get a 5. Its the fifth month in that range. Then we do the same for Febryary, which is the 2nd month in that range. So we have 5 – 2 . So that is 3. However, since we are just telling Excel how wide to make the column, then we always need to add 1 to it. We start on the Feb cell and this counts as 1. Then March is 2, April is 3. So if we just made this array 3 wide, it wouldnt go to May. That is why we need to add 1 to the total to get 4.
Now we can replace the last part of this formula and we get:
Rhw bolded part above is what we just added.
So we end up looking like this:
This is a long and complex formula if you look at it quickly. But if you break it out like we did above, we will see all the components to this and hopefully undertand how this can help you!
For more information on our new product “Master Excel Functions” go to http://www.excelbyjoe.com/excelfunctions