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