Using Index function to define range

Question

Today I had to define a range using the Index function to be used in another formula.

A1:INDEX(B1:B4,2)

I haven't been able to find a way to use anything other than a plain cell reference (like A1) for the start of the range.

Is there a way to make that first reference dynamic? Like using two index functions separated by the semicolon?

Answer 1

Yes, you can use another INDEX for A1 as INDEX returns reference not value. Hence, you can write for example -

INDEX(A1:A10,5):INDEX(B1:B4,2)

Answer 2

"=VLOOKUP(9,A1:INDEX(C1:C6,6),3,FALSE)"

your formula is not dynamic.  If you want to make it dynamic, you need to see the second argument in index dynamic, for example INDEX(C1:C6,COUNTA(C:C))

If you also want A1 to be dynamic, you may actually use two INDEX to make your range, e.g.

=INDEX(A1:A6,2):INDEX(C1:C6,6)

where 2, and 6 are the dynamic parts you need to think about.

Answer 3

A  B  C

1 8   a   x

2 8   b   y

3 9   c   z

4 9   a   x

510  b   y

6 10  c   z

=VLOOKUP(9,A1:INDEX(C1:C6,6),3,FALSE)

I need to be able to move that A1 around dynamically as the cell reference returned by INDEX(C1:C6,6) does. (it returns cell C6).

The second part of the range (index formula) can be dynamic.

But I am still looking for an example of that A1being anything other than a regular cell reference.

Thank you

Answer 4

Hi,

That will not work.  You will have to write the INDEX formula as below and use it within another formula

=INDEX(A1:B4,,)

If you want more specific help, then please post your exact question.