How to define Range?

[kozt77543]

Inside the excel cell, how to define a range with known location of row and column?

For example, I have found out the row of data containing "Test" in the column "B" by using the MATCH("Test",B:B,0) function. Let's say row "30".

Is there any possible way to define the range by using the column starting from cell A1?

Typical way would be

A1:A30

However, is it possible to be something like

A1:A<MATCH("Test",B:B,0)>

Thanks.

[NBVC]

Something like

A1:Index(A:A,Match("Test",B:B,0))

or the more volatile

Indirect("A1:A"&Match("Test",B:B,0))

[kozt77543]

Thanks for the swift reply.

However, for the first solution, somehow I got the error "#VALUE!# when using
A1:Index(A:A,Match("Test",B:B,0))

As for the second solution, it works fine. Thanks!!

[NBVC]

It depends on how you use it.

On it's own will give error

Within another formule should work

[kozt77543]

Thanks. I will try it out.