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

Hi,

If you want to make to range dynamic for more rows that may be added, try this

1. Insert a header row in row 1 and ensure that the headings are in Bold face
2. Select A1:C7 and press Ctrl+T > OK

Your formula will be:

=VLOOKUP(9,$A$2:$B$7,3,FALSE)

As you add data beyond row 7, the range in the formula will auto expand.

Hope this helps.