Arrays question

One more so unpopular question about arrays. First, workbook is attached (Sheet1 !!).

If Index returns an array of values, this array cannot be used further in formulas as a range. Moreover if one presses F9 on this index formula one will not see usual {v1,v2,v3,...}, but either #value! or just one value.

The question is how to convert index output to classical array?

For example, if Index returns:

1 a
2 b
3 c

then how to make it actually to be {1,a;2,b;3,c}???????? Thanks in advance.

=INDEX($B$1:$G$4,{3;4},MATCH($A$2:$A$3,$B$1:$G$1,0)+1) - this formula collects right data from data set, but as you can see rows numbers are specified. It's has to be solved.

Based on the same data set, this formula:
=IF(INDEX($B$2:$G$4,ROW($B$2:$G$4),(TRANSPOSE(MATCH($A$2:A3,$B$1:$H$1,0))))>=$H$2,ROW(INDEX($B$2:$G$4,ROW($B$2:$G$4),(TRANSPOSE(MATCH($A$2:$A$3,$B$1:$H$1,0)))))) finds right row numbers.


all i need now is to substitute 2nd formuls into 1st one, but it can be done, since 2nd formula though displays right rows numbers, cannot be used inside other formulas.

Hope i answered your question.

Thanks again but this way to get the result you need to display it, but i cannot do this, so one formula should produce all output that can be put in other formulas. Thanks again for the effort.

Thank you a lot. It'll take me some time to play with it around. I'll post here the results. So far it looks like an interesting alternative.

Thanks for your help!

i'm trying to make one formula (in fact i have no choice), that:
1. Looks for the names aside and finds these names in the table.
2. Looks for the date aside and finds the earliest date in each "dates column" of the table that which is still greater then than the date of today.
3. Then finds all coefficients, than stand next to the dates from 2 and correspond to the names from 1.

I think you'd better not to get into details, that's why i asked general question. but if you're curios or something please see the last attachment - Sheet 3!

[QUOTE=martindwilson;2788442]ok this gives name next date and coefficient for each name note dates must be in ascending order
all 3 formulas are independent of each other so just use the 3rd row coefficients if you want[/QUOTE

Actually your original solution has the same problem. If the right date happens to be the 1st one then #N/A is displayed. Please help to overcome this.

For this hour here these formulas are too hard to read for me. But as i can see each value in the table is the result of unique formula.

Tell me please can you approach by one formula produce all and only relevant coefficients?

You see, later I'll need to use them as array-variable, with no displaying them on the sheet. So it must be a single formula that produces all relevant array (coefficients)

Thank a lot for assistance!!

it were different questions, but now it's the same discussion

Could be considered as cross post - http://www.mrexcel.com/forum/showthr...=1#post3152092

That is meaning that i need to put this formula on the sheet and expand it over other (3) cells. The problem is that in this case i cant use it as argument in other function without displaying the coefficients themselves on the sheet. "You see, later I'll need to use them as array-variable, with no displaying them on the sheet. So it must be a single formula that produces all relevant array (coefficients) " - let me explain it. will be a regression run 500 times (per every day in last 1.5 year) with different set of the names per every day. The x variable of the regression will be coefficients. Meaning, i cant every time to display them on the sheet. This array of coefficients will be used only inside regression formula. Do you see the problem ?? But know, that i really appreciate your help.