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)))  



ADVERTISEMENT
ADVERTISEMENT