Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]

Today I’d like to introduce you to Excel’s OFFSET Function with 3 real life examples.

At first, I will describe the formula syntax and then I’m going to talk about how the OFFSET function can be used to solve problems in real life.

Introduction

The OFFSET function can return a reference to a cell (let’s call it target cell) or range (target range) that is a specified number of rows and columns away from another cell (reference cell) or range (reference range).

The figure below illustrates how to use the OFFSET function to return the reference to a cell (left part) or a range (right part).

It will give you an intuitive impression on what is a target cell and what is a reference cell.

The cell highlighted in green is a target cell while cells highlighted in yellow consists of a target range.

Cells highlighted in blue are reference cells.

excel offset function image 1

Figure 1

What does OFFSET mean in Excel (syntax)?

Here is the syntax of Offset Function: OFFSET (reference, rows, cols, [height], [width])

ReferenceRequired. The reference is a cell or range of cells from which the offset begins. Please note that the cells must be adjacent to each other if you specify a range of cells.
RowsRequired. The number of rows, up or down, the reference cell or the upper-left cell of the reference range. Rows can be either positive or negative. Look at the left part of Figure 1, the target cell will be B2 if I change the function as OFFSET (C3, -1, -1). B2 is one row up C3.
ColsRequired. The number of columns, to the left or right, of the reference cell or the upper-left cell of the reference range. As with Rows argument, values of Cols can also be both positive and negative. How can we write OFFSET function if we set B4 as a reference cell and C3 as a target cell? The answer is OFFSET (B4, -1, 1). Here you can see that Cols is positive and C3 is one column to the right of B4.
HeightOptional. Only use Height Argument If the target is a range. It tells how many rows that the target range includes. Height must be a positive number. You can see from right part of Figure 1 that there are two rows in the target range. Therefore, we set Height as 2 in that case.
WidthOptional. Only use Width Argument If the target is a range (see right part of Figure 1). It indicates how many columns that the target range contains. Width must be a positive number.

Well, let me now show you how to use the OFFSET function to solve problems in real life.

Case 1: Right-to-Left Lookup by combining OFFSET and MATCH Functions

It’s well known that you can only perform a left-to-right lookup with VLOOKUP function.

The value to search for must be placed in the first column of your table array.

You have to shift your entire table range to the right by one column if you want to add a new lookup value or you need to change your data structure if you’d like to use another column as the lookup value.

But by combining OFFSET together with Match function, the limitation of VLOOKUP function can be removed.

What’s MATCH function and how can we combine OFFSET function with Match function to do the lookup?

Well, Match function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

Let’s take range B3:B8 from Figure 2.1 (which shows revenue of different countries in different years) as an example.

Formula “=MATCH (“USA”, B3:B8, 0)” will return 1 since USA is the first item in the range (see cell B10 and C10).

For another range C2:F2, formula “=MATCH (2015, C2:F2, 0)” returns 3 as 2015 is the third item in the range (see cell B11 and C11).

Going back to OFFSET function.

If we set cell B2 as the reference cell and take cell E3 as the target cell, how can we write the OFFSET formula?

E3 is 1 row below B2 and 3 columns right to B2.

Therefore, the formula can be written as “=OFFSET(B2, 13)”. Look at the numbers in red color closely, can you find that they are matched?

That is the answer to the question – How to combine OFFSET function with Match function – Match function can be applied to serve as the second or the third argument of OFFSET function (see cell C13).

Cell C14 demonstrates how to use VLOOKUP function to retrieve the same data.

We must know revenue in 2015 is recorded in the 4th column of the table array B2:F8 before writing VLOOKUP function.

It means that we have to know very well about the data structure when using VLOOKUP function.

This is another limitation for VLOOKUP. However, by using MATCH function as the argument of OFFSET function, we don’t have to know the column index.

This is very useful if the there are lots of columns.

excel offset function image 2

Figure 2.1

Now let’s move on and see a more complex example.

Suppose that we have a table containing Company Name, Contact Name and Email Address for different companies.

And we want to retrieve company name from a known contact name or get contact name from a known email address. What can we do?

See Figure 2.2, range B5:E8 includes company information. By putting inputs in cell C2 and Cell B3, with the help of formula in red square, I can retrieve company name if I know the contact name.

Range D2:E4 shows how to get contact name with a known email address.

In summary, these two examples illustrate that we can perform a right-to-left lookup and the search value does not need to be placed in the right most column. Any columns in the table array can contain the search value.

excel offset function image 3

Figure 2.2

Case 2: Automate calculation combining OFFSET and COUNT functions

Before introducing on how to automate calculation whenever we add a new number in a column, let’s start with how to return the last number in a column automatically at first.

Look at below figure which shows entries from Human Resources. Suppose that we want to get the last number in Column B, the formula would be “=OFFSET (C2, 9, 0)” if we apply OFFSET function.

From the formula, we can know that 9 is the key number.

As long as we can return this number automatically, we can be able to locate the last number in a column automatically.

9 is just the number of cells that are containing numbers in column C.

If you are familiar with the COUNT function, you will know that COUNT function can count the number of cells that contain numbers in a range.

For example, formula “=COUNT (C3:C11)” will count the number of cells that contain numbers in cells C3 through C11.

In our case, we’d like to know how many numbers in a whole column, therefore, reference like C:C which includes all rows in column C should be used.

Please look at cells G4 and H4, the number returned by “=COUNT(C:C)” is exactly equal to 9.

Thus, by replacing 9 with COUNT(C:C) in above OFFSET function, we can get a new formula “=OFFSET (C2, COUNT(C:C), 0)” (in cell H5).

The number it returns is 87000 which is exactly the last number in column C.

Now let move on to the automatic calculation. Suppose that we want the total of all the numbers in column C.

The formula would be “=SUM (OFFSET (C2, 1, 0, 9, 1))” if we use SUM together with OFFSET.

9 is the total number of rows in range C3:C11 and also the total number of cells contains numbers in column C.

Therefore, we can write the formula in a new way like “=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))”.

Look at cells G10 and H10, the total number of salaries for these 9 employees is $521,700.

Now if you put a number like $34,000 in cell C12, both the number in cell G5 and G10 will be changed as $34,000 and $555,700, respectively.

This is what I call automation as you don’t have to update formulas in cell G5 or G10.

You have to be careful when you use COUNT function as COUNT function only return the number of cells which contain numbers.

For example, “=COUNT (B: B)” returns 0 instead 9 since there is no cell in column B that contains number (see cell G3 and H3).

Column D which includes 10 cells containing numbers and the number returned by “COUNT (D: D)” is also 10.

But if we want to retrieve the last number in column D as what we did for column C, we will get number 0 (see cell G8 and H8).

Obviously, 0 is not what we want. What’s wrong? Cell D13 is 11 rows away from cell D2 instead of 10 rows.

This also can be demonstrated by formula “=OFFSET (D2, COUNT (D: D) + 1, 0)” in cell G7.

In summary, the numbers should be adjacent to each other if we want to use COUNT function together with OFFSET function to enable automation of calculation.

excel offset function image 4

Figure 3

Case 3: Use OFFSET function to make a dynamic range

Suppose that we want to chart a company’s monthly unit sales and Figure 4.1 shows current data and chart created based on current data.

Each month, the most recent month’s units’ sales will be added below the last number in column C.

Is there an easy way to update chart automatically?

The key to updating the chart is to use OFFSET function to create dynamic range names for the Units Sold column.

The dynamic range for units’ sales will automatically include all sales data as new data is entered.

excel offset function image 5

Figure 4.1

To create a dynamic range, click the Formulas tab and, then choose Name Manager or Define Name.

Below New Name dialog box will prompt if you click on Define Name.

If you choose Name manager, you also need to click on New to make below New Name dialog box appear.

excel offset function image 6

Figure 4.2

In the “Name:” input box, the dynamic range name should be filled in. And In the “Refers to:” input box, we need to type the OFFSET formula “=OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C: $C), 1)” that would generate a dynamic range of values based on Units Sold values typed in the column C.

By default, a name will apply to the whole workbook and must be unique within the workbook.

However, we want to restrict the scope to a particular sheet.

Therefore, we choose Figure4 here in the “Scope:” input box. After clicking on OK, the dynamic range is created.

It will automatically include all sales data as new data is entered.

Now right click on any point in the chart and then select “Select Data”.

excel offset function image 7

Figure 4.3

In the prompted Select Data Source, choose Series1 and then Edit.

excel offset function image 8

Figure 4.4

And then type “=Figure4!Units” as Figure 4.5 shows.

excel offset function image 9

Figure 4.5

Finally, let’s have a try and type 11 in cell C13. You can see the chart has changed and value 11 has been included.

The chart will change automatically when new data is added.

excel offset function image 10

Figure 4.6

Read More…

Download working files

Download the working files from the link below.


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

      Leave a reply