OFFSET() function

Functions Jun 07, 2019

About  OFFSET() function

The OFFSET() function is a lookup and reference function that returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range.

Where to apply :

This function helps in getting data from master table and to map the data.

Syntax :

=OFFSET(reference, rows, cols, [height], [width] )

Parameters :

reference - Any cell reference.

rows - A positive or negative number by the number of cells left to both up and down side of used reference.

cols - A positive or negative number by the number of cells left to both left and right side of used reference.

[height] - A number that defines height of a range.

[width] - A number that defines width of a range.

Example:

1. Let's suppose the cell A1 is the reference from where you need to get the value of 2nd row and 3rd column that means D3 and the cell D3 keeps 5.


=OFFSET(a1, 2, 3)    // Returns (5).

It returns 5.

Excel based example:

Lets take a table to understand the use of OFFSET() function.

Image 1 - Table

1. From the above we need to get Quantity, Price and Final Price for a specific product.

Image 2 - OFFSET() function

Formulas applied to get result for Quantity, Price and Final Price.

The OFFSET() function is well explained in above image.

Hope, it would have added in your Excel function knowledge.

Thank You!

Best Excel training institute in Noida provides Basic Excel, Advance Excel and VBA offline and online classes.

If you want to learn Excel & VBA courses to be Excel expert, connect us on:

ExcelHour
9599408486 / 8929231250
Website: https://www.excelhour.in/

Manoj kumar

Corporate Trainer at ExcelHour. I have devoted over 9 years to various industries for learning multiple data challenges and trained Excel and VBA courses to the corporate along with offline training.

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.