Cell Reference Named Ranges as Arrays

[smitpau]

Hi,

In an excel file we have various named ranges.

Ideally in a column we could reference these as arrays in an INDEX formula as a name so the array would change depending on cells in a column, just like how the row or column reference numbers can change.

Do you know if this is possible as can't seem to make it work.

Thanks for reading.

 

[jasonb75]

It is possible, without seeing exactly what you're trying to do it is impossible to say where you're going wrong.

You should be able to use the name anywhere that you would use the range that it refers to.

 

[smitpau]

This is just an illustrative but still representative example.

So in this case cells I6:I7 and J6:J7 are named UK & US respectively.

For the INDEX formula the problem is I'd like the array to dynamically change the name depending on the cell it's referencing (but it won't), this though is fine for the column refence (cells A6, A7).

Basically I'd like to be able to drag the INDEX formula down and it change the named range it uses.

Book1
	A	B	C	D	E	F	G	H	I	J
4								Data Table
5	Column	Name			2019				UK	US
6	1	UK		UK	UK			2019	1.1	1.2
7	1	US		US	US			2018
Sheet1

Cell Formulas
Range	Formula
E6:E7	E6	=INDEX(B6,A6)

 

[jasonb75]

If you typed I6:I7 into B6, then the formula would not work because the INDEX formula would see "I6:I7" as text, not as a range. It is no different with a name range.

To use the text in a cell as a range, you need to use the inefficient INDIRECT function. =INDEX(INDIRECT(B6),A6)

It would be better to use index properly and match to the data table headings. =INDEX(UK:US,MATCH($E$5:$H$6:$H$7,0),MATCH($B6,$I$5:$J$5,0))

 

[smitpau]

Thanks for the reply.

Ideally as this formula would be used quite extensively it would avoid INDIRECT and Volatile functions is general to avoid slowing down the spreadsheet.

When I try the formula below, this does not retrieve the 1.1 and 1.2 values, as I'm not sure the UK:US as the arrays works.

=INDEX(UK:US,MATCH($E$5:$H$6:$H$7,0),MATCH($B6,$I$5:$J$5,0))

That has helped though as if I use the area-num I can then specify the named range, like below.

Book1
	A	B	C	D	E	F	G	H	I	J
5	Column	Name			2019				UK	US
6	1	UK		UK	1.1			2019	1.1	1.2
7	1	US		US	1.2			2018
Sheet1

Cell Formulas
Range	Formula
E6	E6	=INDEX((UK,US),1,1,1)
E7	E7	=INDEX((UK,US),1,1,2)

 

[smitpau]

The next step would be to try and make the named ranges indexable, so instead of having to type out UK, US etc it could be another named range used to look up the named range arrays if that makes sense.

 

[jasonb75]

The formula had a typo, but it wouldn't have worked anyway. If your names were longer then it would work but because UK and US are actually columns in the sheet anyway, the formula uses UK:US as column references, not named ranges.

make the named ranges indexable

So you want =INDEX((named array of names),1,1,1) then that would go back to using INDIRECT.

You could always format your data as a table, that way it would be named for you and the method I used would work.

 

[smitpau]

Thanks for the help.

Shame the only way that seems possible is using INDIRECT.