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.

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

<colgroup><col><col><col span="16"></colgroup><tbody>
</tbody>

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.