Get an Index range based on a cell value

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
174
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!
Don't worry, I have figured this out now. I didn't think of CONCENTATE and INDIRECT mixed together
 
Upvote 0
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 =)
 
Upvote 0
No problem :) Glad I could help (y) Appreciate the feedback!
 
Upvote 0
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
 
Upvote 0
Sure, instead of typing out the range in the INDEX function, use INDIRECT("C"&A1&":C10000")
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top