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

Dependent Drop-down List in Excel

Excel forms are used to fetch data from the end user. When we have to give choices, we often use the drop-down feature of Excel. In this tutorial, we won’t...

7 minutes read.

Excel IFERROR() Function

The IFERROR() function in excel returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument. When...

2 minutes read.

Excel WEEKDAY() Function

The WEEKDAY () function in excel returns number from 1 (Sunday) to 7 (Saturday) identifying the day of the week based on its date. This function Default return is - Sunday would be...

1 minute 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.

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.

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.

Row Difference in Excel

Microsoft Excel is used for multiple purposes in various organizations. It is a combination of numeric values, alphabets etc. To calculate data, the user organizes the data according to their...

5 minutes read.

Application Interface and Key Components

The essential key components of Excel are as follows: Ribbon TabQuick Access ToolbarFormula BarActive CellColumnsRows Name BoxSheet TabsZoom ControlStatus Bar Ribbon Tab: The ribbon includes tabs labeled Home, Insert, Page Layout, Formulas, Data, Review,...

3 minutes read.

Excel INT() Function

Excel INT() Function The INT() function in excel is used to round a number down to the nearest integer. It removes decimal points from any number value & return rounded down number value in...

1 minute read.

Excel AND() Function

The AND() function in excel tests the multiple sets of conditions and returns TRUE if all supplied conditions met the criterion, FALSE otherwise. You need to have a minimum of 2 conditions...

2 minutes read.

Quarter Dates in Excel

Excel is a combination of numeric values and alphabets. The user's project consists of the date depending upon their accounting and financial analysis. Sometimes there is a need to find...

4 minutes read.

AutoFill in Excel

AutoFill in Excel Excel is known for its credibility to operate repeated and similar tasks automatically. For this purpose, it facilitates an inbuilt feature known as Autofill that recognizes the series of...

3 minutes read.

Multiplication in Excel

Multiplication plays a vital role in Mathematics. Usually, the calculations are performed manually and automatically. Manual calculations take time and sometimes leads to error, and it is a challenging process...

5 minutes read.

Reverse List in Excel

What is Reverse list in Excel? Microsoft Excel is widely used for calculation purposes like statistical and analytical for predicting and getting the result. Sometimes for calculation purposes, the user needs...

5 minutes read.

Excel ROUND() Function

Excel ROUND() Function The ROUND() function in excel rounds the numbers to specified digits. Syntax ROUND(Number,num_digits) Parameter Number(required)- This parameter represents the number which you want to round up. num_digits(required)- This parameter represents the number of decimal places to which...

1 minute read.

Excel EDATE() Function

Excel EDATE() Function The EDATE() function in excel returns the serial number of the date that is the indicated number of months before or after the start date. Syntax EDATE(Start_Date, Month) Parameter Start_Date(required)- This parameter represents...

1 minute read.

Paste Options in Excel

Excel spreadsheet is a combination of numeric values, alphabets which is used for calculation purposes. While performing the calculations, there is a need to repeat the data for further process....

6 minutes read.

Trendline in Excel

A Trendline is a line imposed on a graph to predict the direction of the data. It connects the series of data together to showcase the data best fit. The...

3 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.

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.