INDEX reference questions

[awoohaw]

I often use INDEX to create arrays by using the reference characteristic it has. One of the questions earlier in the forum had me using this function and I thought it would work.
But, i'm getting a value error with the calculation to put together the two index halves of the range. It finds the value of each half correctly, but when put together the result is a #VALUE error. I'm not sure why since its using column numbers and numbers in the matching cells. But, I converted all cells to Number format anyway. And still get the error.

I do not get the error when I just use a range reference versus the range calculated using the INDEX functions.

This is a question about the INDEX function, and not the task.

Any thoughts?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	9	11							1	1	1
2	9			=INDEX(COLUMN(1:1)*(COLUMN(1:1)>=A1)*(COLUMN(1:1)<=B1),A1)
3	11			=INDEX(COLUMN(1:1)*(COLUMN(1:1)>=A1)*(COLUMN(1:1)<=B1),B1)
4	#VALUE!			= INDEX(COLUMN(1:1)*(--(COLUMN(1:1)>=A1))*(--(COLUMN(1:1)<=B1)),A1) : INDEX(COLUMN(1:1)*(--(COLUMN(1:1)>=A1))*(--(COLUMN(1:1)<=B1)),B1)
5
6	1	1	1	=INDEX(1:1,9):INDEX(1:1,11)
Sheet1

Cell Formulas
Range	Formula
A2	A2	=INDEX(COLUMN(1:1)*(COLUMN(1:1)>=A1)*(COLUMN(1:1)<=B1),A1)
A3	A3	=INDEX(COLUMN(1:1)*(COLUMN(1:1)>=A1)*(COLUMN(1:1)<=B1),B1)
A4	A4	= INDEX(COLUMN(1:1)*(--(COLUMN(1:1)>=A1))*(--(COLUMN(1:1)<=B1)),A1) : INDEX(COLUMN(1:1)*(--(COLUMN(1:1)>=A1))*(--(COLUMN(1:1)<=B1)),B1)
D2:D4,D6	D2	=FORMULATEXT(A2)
A6:C6	A6	=INDEX(1:1,9):INDEX(1:1,11)
Dynamic array formulas.

 

[RoryA]

Those INDEX functions are using arrays, not ranges, so you can't use them with the reference operator.

 

[awoohaw]

Those INDEX functions are using arrays, not ranges, so you can't use them with the reference operator.

well, isn't that just a raspberry to microsoft. blblblblbllbbbb

 

[RoryA]

well, isn't that just a raspberry to microsoft

Um, no. You're multiplying column numbers together. Hard to see how that should return a range.