Dynamic Index Array

[smitpau]

Hi,

I'm not sure if this is possible so would appreciate any responses.

I'd like to be able to use a named range in an index but would like the named range to be dynamic.

For example ideally the formula would be like =index(cell reference to dynamic array, etc).

The problem is I don't believe you can cell reference a named range.

I believe I'll have to list all the named ranges and then make the area_num dynamic which is not the most practical solution.

Thanks for reading,

Paul

 

[Fluff]

Do you mean you want to put the name of a named range in A1 & then use =index(A1,...)

 

[Eric W]

Do you mean this?

Book1
	A	B	C	D	E	F	G	H	I
1		Range1	Range2	Range3	Range4		Range	Offset	Result
2		Dog	Car	California	Orange		Range3	3	Oregon
3		Cat	RV	Washington	Blue
4		Mouse	Truck	Oregon	Green
5		Iguana	Bus	Nevada	Red
Sheet13

Cell Formulas
Range	Formula
I2	I2	=INDEX(INDIRECT(G2),H2)

 

[smitpau]

Thanks both for the quick responses.

It's like Fluff described.

So say there are 10 named ranges, an IF formula could return the specific named range for the array I'm after but it wouldn't recognise it as text.

 

[Fluff]

Eric has shown you how to do it.

 

[smitpau]

Great thanks both!

 

[Eric W]

Glad we could help! Thanks for the feedback.

 

[smitpau]

This may bit a bit fussy and there may not be an answer to this but here goes anyway.

Is there a way to do this without using a volatile function such as INDIRECT (conscious this logic repeated could slow a file down).

Thanks

 

[Eric W]

I am not aware of a way to do this without a volatile function, at least in the general sense. Depending on how your sheet is laid out, and where your defined ranges are, you could do something like:

Book1
	A	B	C	D	E	F	G	H	I
1		Range1	Range2	Range3	Range4		Range	Offset	Result
2		Dog	Car	California	Orange		Range3	3	Oregon
3		Cat	RV	Washington	Blue
4		Mouse	Truck	Oregon	Green
5		Iguana	Bus	Nevada	Red
Sheet3

Cell Formulas
Range	Formula
I2	I2	=INDEX($B$2:$E$5,H2,MATCH(G2,$B$1:$E$1,0))

This assumes the ranges are all adjacent. Depending on how your ranges are arranged, there might be a way.