awoohaw
Well-known Member
- Joined
- Mar 23, 2022
- Messages
- 4,629
- Office Version
- 365
- Platform
- Windows
- Web
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?
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?
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. |