Dynamic selection of table arrays

[wilfrid147]

Hi there,

Is there any way of selecting a table array based on an identifier (i.e 1). I am trying to perform an index(match) within these tables but I am having trouble in producing an array formula (i.e $A$3:$C$15).

My guess is that I should be mapping identifiers (i.e 1,2,3,4,5 etc) to array formulae. I've shown two tables below for your reference, both of which already have an identifer next to the row "Time %". These tables have the same height but have different widths.

[TABLE="width: 2285"]
<colgroup><col><col><col span="16"></colgroup><tbody>[TR]
[TD][TABLE="width: 2396"]
<colgroup><col><col><col><col span="16"></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD] Time[/TD]
[TD]%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]12.50%[/TD]
[TD="align: right"]18.75%[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]31.25%[/TD]
[TD="align: right"]37.50%[/TD]
[TD="align: right"]43.75%[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]56.25%[/TD]
[TD="align: right"]62.50%[/TD]
[TD="align: right"]68.75%[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]81.25%[/TD]
[TD="align: right"]87.50%[/TD]
[TD="align: right"]93.75%[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Construction[/TD]
[TD]%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]6.52%[/TD]
[TD="align: right"]9.83%[/TD]
[TD="align: right"]19.20%[/TD]
[TD="align: right"]25.12%[/TD]
[TD="align: right"]31.23%[/TD]
[TD="align: right"]37.45%[/TD]
[TD="align: right"]52.80%[/TD]
[TD="align: right"]61.12%[/TD]
[TD="align: right"]69.24%[/TD]
[TD="align: right"]76.35%[/TD]
[TD="align: right"]87.60%[/TD]
[TD="align: right"]91.51%[/TD]
[TD="align: right"]94.81%[/TD]
[TD="align: right"]96.91%[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]01/09/2018[/TD]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]01/12/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Land Cost[/TD]
[TD]-£ 2,828,645[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Development Cost[/TD]
[TD] £ -[/TD]
[TD]-£ 1,276,131[/TD]
[TD]-£ 649,619[/TD]
[TD]-£ 1,834,199[/TD]
[TD]-£ 1,158,388[/TD]
[TD]-£ 1,197,652[/TD]
[TD]-£ 1,218,655[/TD]
[TD]-£ 3,005,218[/TD]
[TD]-£ 1,629,360[/TD]
[TD]-£ 1,589,215[/TD]
[TD]-£ 1,392,258[/TD]
[TD]-£ 2,204,075[/TD]
[TD]-£ 765,355[/TD]
[TD]-£ 646,682[/TD]
[TD]-£ 410,510[/TD]
[TD]-£ 605,753[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rolled Up Interest[/TD]
[TD] £ 137,010[/TD]
[TD] £ 57,862[/TD]
[TD] £ 27,450[/TD]
[TD] £ 71,863[/TD]
[TD] £ 41,832[/TD]
[TD] £ 39,587[/TD]
[TD] £ 36,564[/TD]
[TD] £ 81,031[/TD]
[TD] £ 38,994[/TD]
[TD] £ 33,230[/TD]
[TD] £ 24,916[/TD]
[TD] £ 32,821[/TD]
[TD] £ 9,104[/TD]
[TD] £ 5,761[/TD]
[TD] £ 2,434[/TD]
[TD] £ 1,793[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Remaining Balance[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD]-£ 2,490,284[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Outflow[/TD]
[TD]-£ 2,828,645[/TD]
[TD]-£ 1,276,131[/TD]
[TD]-£ 649,619[/TD]
[TD]-£ 1,834,199[/TD]
[TD]-£ 1,158,388[/TD]
[TD]-£ 1,197,652[/TD]
[TD]-£ 1,218,655[/TD]
[TD]-£ 3,005,218[/TD]
[TD]-£ 1,629,360[/TD]
[TD]-£ 1,589,215[/TD]
[TD]-£ 1,392,258[/TD]
[TD]-£ 2,204,075[/TD]
[TD]-£ 765,355[/TD]
[TD]-£ 646,682[/TD]
[TD]-£ 410,510[/TD]
[TD]-£ 2,453,785[/TD]
[TD] £ - [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] Time[/TD]
[TD]%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]16.67%[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]33.33%[/TD]
[TD="align: right"]41.67%[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]58.33%[/TD]
[TD="align: right"]66.67%[/TD]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]83.33%[/TD]
[TD="align: right"]91.67%[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Construction[/TD]
[TD]%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]9.82%[/TD]
[TD="align: right"]19.20%[/TD]
[TD="align: right"]25.14%[/TD]
[TD="align: right"]37.42%[/TD]
[TD="align: right"]52.80%[/TD]
[TD="align: right"]61.15%[/TD]
[TD="align: right"]76.32%[/TD]
[TD="align: right"]87.60%[/TD]
[TD="align: right"]91.52%[/TD]
[TD="align: right"]96.90%[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Month[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]01/09/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Land Cost[/TD]
[TD]-£ 4,700,000[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Development Cost[/TD]
[TD] £ -[/TD]
[TD]-£ 791,240[/TD]
[TD]-£ 756,576[/TD]
[TD]-£ 478,909[/TD]
[TD]-£ 990,226[/TD]
[TD]-£ 1,239,543[/TD]
[TD]-£ 673,461[/TD]
[TD]-£ 1,222,613[/TD]
[TD]-£ 909,342[/TD]
[TD]-£ 316,174[/TD]
[TD]-£ 433,792[/TD]
[TD]-£ 249,666[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rolled Up Interest[/TD]
[TD] £ 217,034[/TD]
[TD] £ 33,429[/TD]
[TD] £ 29,004[/TD]
[TD] £ 16,492[/TD]
[TD] £ 30,254[/TD]
[TD] £ 33,075[/TD]
[TD] £ 15,374[/TD]
[TD] £ 23,214[/TD]
[TD] £ 13,787[/TD]
[TD] £ 3,588[/TD]
[TD] £ 3,276[/TD]
[TD] £ 941[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Remaining Balance[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD]-£ 1,278,459[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Outflow[/TD]
[TD]-£ 4,700,000[/TD]
[TD]-£ 791,240[/TD]
[TD]-£ 756,576[/TD]
[TD]-£ 478,909[/TD]
[TD]-£ 990,226[/TD]
[TD]-£ 1,239,543[/TD]
[TD]-£ 673,461[/TD]
[TD]-£ 1,222,613[/TD]
[TD]-£ 909,342[/TD]
[TD]-£ 316,174[/TD]
[TD]-£ 433,792[/TD]
[TD]-£ 1,108,657[/TD]
[TD] £ - [/TD]
[TD] £ - [/TD]
[TD] £ - [/TD]
[TD] £ - [/TD]
[TD] £ - [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to do this without the use of volatile functions and without manually mapping identifiers to arrays (i.e if(...=1, Array 1, if(...=2, Array 2...) etc.) because I have many of these tables. Thanks.

 

[steve the fish]

Do these tables all have the same 'headers' down the side?

 

[wilfrid147]

Do these tables all have the same 'headers' down the side?

yes they do.

 

[steve the fish]

If so this one is a possible but does require entry of the ranges:

=VLOOKUP($V$2,CHOOSE($V$1,B1:S9,B11:S19),2,0)

Or this one which requires same headers in the same positions and equal number of rows for each table:

=INDEX(C:C,MATCH($V$2,$B$1:$B$9,0)+(10*($V$1-1)))

V1 contains table number. V2 contains header to be searched for.

 

[wilfrid147]

Hi steve,

I don't think the formula achieves what I'm trying to produce as I'd like to produce an array formula ie A2:B9 based on an identifier. As you said, the number of rows is fixed but the number of columns is dynamic. I also don't wish to use the choose function to achieve this as I have 20 sets of arrays and each array has a different number of columns which might change some time in the future. Thanks for your help.


Wilfrid

 

[steve the fish]

Did you try the other formula. As long as your tables fulfil the criteria I mentioned it will work.

 

[Aladin Akyurek]

You'll need INDIRECT if you can't use CHOOSE.

 

[wilfrid147]

Yes but that formula returns a cell value rather than a cell range (array) which I hope to use within another index match?

 

[wilfrid147]

You'll need INDIRECT if you can't use CHOOSE.

Unfortunately, indirect is also another volatile function which I want to avoid.