Excel OFFSET () Function

Excel OFFSET () Function

The OFFSET() function in excel returns range of cells that is a specified number of rows and columns from an initial specified range.

Syntax

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

Parameter

reference(required)- This parameter represents a cell reference or range which is a starting point and to be offset.

Rows (required)- This parameter refers to the number of rows to offset below the supplied reference.

Cols (required)- This parameter represents the column to the right of the starting reference.

height(optional)- This parameter refers to the height in rows of the returned reference.

width(optional)- This parameter refers to the width in columns of the returned reference.

Category

Lookup & Reference

Return

This function returns range of cells that is a specified number of rows and columns from an initial specified range.

Example 1

Objective: Get the dynamic sum of column name mentioned in Lookup value cell

Name	Purchased Date	Product Category	Product	# Unit	Amount
Sukla Rani	21-Mar-16	Phone	HP 15-ay011nr	2	$989
Rahul George	8-Jun-16	Palmtop	Pebble Smart Watch	2	$753
Sid Rao	11-Jan-14	Watch	Acer Aspire E 15	1	$871
Jenny Zame	16-Jul-15	Alexa	Fitbit Blaze Smart Watch	4	$637
William Laslo	31-Mar-14	Watch	Nikon D3200	3	$573
Charlie Den	22-Nov-15	Desktop	Apple Watch Sport	4	$662
Bing jing	31-May-15	Speaker	ASUS F556UA	2	$975
Paul White	8-Jun-16	Laptop	Samsung Gear S2	3	$501
Ahmed Bilal	1-Jan-14	Watch	ASUS ROG	2	$556
Hans Raj	16-Jun-16	Laptop	Nikon D4S	1	$968
Christine	21-Dec-15	Camera	Canon ES	1	$600

Look up Value	Result	Formula
#Unit	25	=SUM(OFFSET(B174,1,MATCH(D189,B174:F174,0)-1,COUNTA(E175:E190)))

Related Topics

Excel IF() Function

Excel IF() Function: The IF() function in excel checks whether a condition is true or not and returns one value if the condition is TRUE and another value if it's...

2 minutes read.

Excel HOUR() Function

Excel HOUR() Function The HOUR() function in excel converts a serial number to an hour (0 - 23). Syntax HOUR(SERIAL_NUMBER) Parameter SERIAL_NUMBER (required)- This parameter represents the date or time from which you want to extract...

1 minute read.

SUM Functions in Excel

The BODMAS formula plays a vital role in the concept of mathematics. BODMAS rule follows the Bracket of Division, Multiplication, Addition and Subtraction. This rule is followed for a correct...

6 minutes read.

How to Alphabetize in Excel?

Alphabetize in Excel One of the reasons for Excel's widest popularity is its ability to swiftly and effortlessly sort data. Excel facilitates easy methods that alphabetically sort the lists of strings...

5 minutes read.

Cells and Ranges in Excel

Cells and Ranges Each cell is identified by its cell address, which is a combination of the column and row on which that cell is situated. A group of cells together are called ‘Range’. When...

5 minutes read.

Copying formula in Excel

Copy a formula in Excel While working with statistical calculations in Microsoft Excel, the user uses formulas to calculate the mathematical operations. Implementing these formulas manually for each data is a...

3 minutes read.

Excel TEXT() Function

The TEXT () function in excel converts a supplied numerical value into the text of a specific format. Syntax TEXT(Value, Format_text) Parameter Value (required)- This parameter represents the value for which we need to...

1 minute read.

Adding Column in Excel

Excel spreadsheet is a combination of rows and columns. After creating the table, there is a need to insert additional row or columns. To organize a better worksheet for calculations,...

5 minutes read.

Excel MATCH () Function

Excel MATCH () Function The MATCH () function in excel locates the position of a lookup value in a row, column, or table and returns the relative position of an item in an array. Syntax MATCH...

2 minutes read.

What is a name box in Excel?

The name box in Microsoft Excel specifies a name for a single cell or a table. The name box is present in the top left corner, left to the formula...

4 minutes read.

Separate Strings in Excel

How to Separate String in Excel Day by day, Microsoft Excel is increasing for business and personal use. It is a combination of numbers and alphabets based on the data provided....

4 minutes read.

Operators in Excel

In MS Excel, operators are used to defining the operation you want to perform on the used elements or between variables. There are four types of operators: Arithmetic operators, Comparison...

4 minutes read.

How to create drop down in excel?

How to create drop down in excel? Drop-down lists are mostly used to facilitate data entry operations. They are commonly used in interactive websites or applications. Microsoft has expanded his Excel's data validation feature...

5 minutes read.

Excel OFFSET () Function

Excel OFFSET () Function The OFFSET() function in excel returns range of cells that is a specified number of rows and columns from an initial specified range. Syntax OFFSET (reference, rows, cols, [height], [width]) Parameter reference(required)-...

2 minutes read.

Notes in Excel

Notes in Excel Notes are used to include additional information to the cell and explain formulas calculation, any modification made to the cell, providing necessary information on data that is entered or to provide any...

3 minutes read.

Histogram in Excel

What is Histogram in Excel? “ A histogram is a graphical tool familiar for summarizing discrete or continuous data, where the data are measured on an interval scale.” Karl Pearson described this...

5 minutes read.

What if Analysis Data Table in Excel

What is What-If Analysis? Suppose you have a cell A15 which is formula driven i.e. has a formula and the formula is based on values in different cells (A1, A2, A3, A4), what-if...

4 minutes read.

Text to Columns

Text to Columns This Excel feature is used to split the cell content name of one cell into multiple columns based on a delimiter, such as a space or a special character or based...

2 minutes read.

Excel WORKDAY.INTL() Function

Excel WORKDAY.INTL() Function The WORKDAY.INTL() function in excel returns a date before or after a specified number of workdays with custom weekend parameter. Syntax WORKDAY.INTL (START_DAY, DAYS,[WEEKEND],[HOLIDAYS]) Parameter START_DAY (required)- This parameter represents the initial date...

2 minutes read.

How to Make Use of the F-Test in Excel

Intro to F-Test in Microsoft Excel F-Test is primarily considered to be the essential tool of statistical in Microsoft Excel that, in turn, is used to do the hypothesis test with...

7 minutes read.