trouble understanding the OFFSET function

[Special K]

I am having some trouble understanding the functionality of the OFFSET function. I have the following sample worksheet:

foo_test.xlsx
	A	B	C	D	E
1	Date	Data		OFFSET(B2,0,0,1)	1
2	1/1/2023	$1.00		OFFSET(B2,0,0,2)	1
3	1/2/2023	$2.00		OFFSET(B2,0,0,3)	2
4	1/3/2023	$3.00		OFFSET(B2,0,0,4)	3
5	1/4/2023	$4.00		OFFSET(B2,0,0,5)	4
6	1/5/2023	$5.00		OFFSET(B2,0,0,10)	5
7	1/6/2023	$6.00		OFFSET(B2,0,0,19)	6
8	1/7/2023	$7.00
9	1/8/2023	$8.00
10	1/9/2023	$9.00
11	1/10/2023	$10.00
12	1/11/2023	$11.00
13	1/12/2023	$12.00
14	1/13/2023	$13.00		OFFSET(B2,0,0,1)	1
15	1/14/2023	$14.00		OFFSET(B2,0,0,2)	#VALUE!
16	1/15/2023	$15.00		OFFSET(B2,0,0,3)	#VALUE!
17	1/16/2023	$16.00		OFFSET(B2,0,0,4)	#VALUE!
18	1/17/2023	$17.00		OFFSET(B2,0,0,5)	#VALUE!
19	1/18/2023	$18.00		OFFSET(B2,0,0,10)	#VALUE!
20	1/19/2023	$19.00		OFFSET(B2,0,0,19)	19
Sheet1

I have the same set of OFFSET formulas in cells E1:E7 and E14:20. Here are my questions:

1. What number is supposed to be displayed in the cell when the OFFSET function returns a range of values? For example, both OFFSET(B2,0,0,1) and OFFSET(B2,0,0,2) return 1, which is the first number in the range, but OFFSET(B2,0,0,3) returns 2, which is the second number in the range. What is the pattern here?

2. Why do the formulas in cells E2:E6 return a number, but the exact same formulas entered into cells E15:E19 return #VALUE!? The reference cell, B2, is identical in all cases.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Special K]

I just updated my profile. I'm using Office 2010 and Windows 10.

 

[Fluff]

Thanks for that.
The formula is using what is known as implicit intersection. What that means is it is trying to pull the value from the same row the formula is on.
So E3:E7 & E20 are pulling the value from col B on their respective rows, but E15:E19 return an error as the array created by offset does not cover the rows those formulae are on.