Get an Index range based on a cell value

[jeremypyle]

If there is a formula in cell a1 and the result of this formula is "171", how do I make it so that in b2, I can say, grab text from c(a1) or c171?

Is this possible? I think you may need to use INDIRECT function but I haven't figured it out and haven't been able to.

In b2, it will grab text from c171 if 171 is the result of a formula in a1. However, it the result of the formula in a1 was 98, then b2 would be grabbing text from c98.

I'm not sure how this is done

 

[BenMiller]

Try =INDIRECT("C"&A1)

 

[jeremypyle]

Don't worry, I have figured this out now. I didn't think of CONCENTATE and INDIRECT mixed together

 

[jeremypyle]

Oh thanks so much Ben. Your's works better than mine. That is what I was wanting to find out. I understand the concept of INDIRECT now I think. Thank you very much =)

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!

 

[jeremypyle]

Oh, one more question which is similar. How do you apply this function with the another function. For example, in b1, could you have range of c(a1):$c$10000?

Or in other words:

c171:$c$10000

Is this possible?

I want to be able to use c171 in other formulas such as INDEX etc

Thanks

 

[BenMiller]

Sure, instead of typing out the range in the INDEX function, use INDIRECT("C"&A1&":C10000")