# Get an Index range based on a cell value

#### jeremypyle

##### Board Regular
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try =INDIRECT("C"&A1)

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

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 =)

No problem Glad I could help Appreciate the feedback!

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

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

Replies
8
Views
213
Replies
1
Views
311
Replies
3
Views
293
Replies
7
Views
247
Replies
1
Views
421

1,211,789
Messages
6,103,986
Members
447,889
Latest member
birdman15

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back