Dynamic Range Using INDEX instead of Offset

[Guest]

Need Help to learn this:

Dynamic Range Using Index().

I can do this:
=offset(A1,0,0,CountA(A:A),CountA(1:1))

But I am trying to do it using Index Function instead of Offset.


Here are the data.

---A---------B
1--x1-------xx
2--x2-------xx
3--x3-------xx
4--x4-------xx
5--x5-------xx
6--x6-------xx
7--x7-------xx
8--x8-------xx

This can be done:
Dynamic Range Row: x1 to x8
=$A$1:INDEX($A:$A,COUNTA($A:$A))
RESTRICTED TO COLUMN A.


I have tried filling the Index Syntax: but it doesn't work.
Array------->A:A
Row_No------>CountA(A:A)
Column_No--->CountA(1:1)


Summary:
A Dynamic Range using Index (reads Rows & Columns).

With this, I can Incorporate this into a Vlookup(C1,[Here!!!],2,false) as an
example.

If it is not possible, just give me a holler!!!
Thanks.

 

[Guest]

You can incorporate the OFFSET into the VLOOKUP. Is this the reason you are
trying to find a workaround?

 

[Guest]

Dear Barb,

Thanks for responding.

It's not the reason. So far I know of 2 types of dynamic range:
1. Offset
2. Indirect + CountA - for array formulas

Now, I am looking for the Index.
=$A$1:INDEX($A:$A,COUNTA($A:$A)) <-- only reads 1 column.
But I want one that reads more than 1 column, using the Index.

I had a feeling it's not quite possible..lol.

 

[Jon Peltier]

You need to specify two opposite corners, not necessarily top left and
bottom right. This does bottom left and top right:

=INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)):INDEX(Sheet1!$1:$1,COUNTA(Sheet1!$1:$1))

I don't know your objection to OFFSET for arrays, but INDEX has the
advantage of not being volatile.

- Jon

 

[Guest]

Dear Jon,
You read my mind about the volatile part. And John, thank you. The formula
works and helpful.