Using a cell reference indirectly in a formula

[doeh]

Hello,

I have a spreadsheet containing multiple cases arranged horizontally (each case muliple columns) with time depending changes vertically. 

	Case1_Value1	Case1_Value2	Case1_Value3	Case2_Value1	Case2_Value2	Case2_Value3
t1	1	3	2	3	2	1
t2	4	4	5	3	6	3
t3	...

I need the position of the maximum for each case, like

	Case1	Case 2
t1	Case1_Value2	Case2_Value1
t2	Case1_Value3	Case2_Value2
t3	...

Since the numbers of columns in which this matrix is safed is at the maximum, I need to extract this information on a separate sheet. Since I know how many columns each case has I can calulate the number of the columns for each case.

I have managed to extract the maximum: =MAX(INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix")))

for which in D4 is the row number and in E2 and F2 I calculated the column number (min and max). However, if I try to extract the position of the maximum, I get an error.

=INDEX(Matrix!B$1:EP$1,MATCH(MAX(INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix"))),INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix")))

Any help is much appreciated!

Thanks

[#2]

@doeh 

May I make a suggestion?

Your request for help has been up for 9 hours with no response. My guess is that people don't really know where to begin. You show an elaborate formula that doesn't work, and a mock-up of the actual spreadsheet where that formula doesn't work. You would help us help you if you posted the actual spreadsheet, so we wouldn't have to create a small and possibly off=base copy based on that little glimpse you give. Just make sure it contains no confidential information.

And if you could describe just a bit more of the context here. What is the task, the bigger task, of which this spreadsheet is just a part? Why is it laid out as it is? How long has it been going on, to now have reached the limits of Excel? That kind of stuff.

[#3]

@doeh Although i agree with @mathetes I think I see your problem.  I must also would try to avoid using indirect.  You could use INDEX() or better yet just NAME the ranges or at least use the LET() function if you have that available so excel doesn't have to recalculate those references each time.   That said I believe your problem is that your MATCH doesn't declare it to be an exact match and I think you were missing a ")".  I also don't understand why you have the range 2x.  Try this:

=INDEX(Matrix!B$1:EP$1,MATCH(MAX(INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix"))),INDIRECT(ADDRESS(D4+1,E$2,,,"Matrix")):INDIRECT(ADDRESS(D4+1,F$2,,,"Matrix")),0))

[#4]

@doeh 

Mockup for dynamic array variant

could be

=LET(
   h, $C$2:$H$2, t, $B$3:$B$4, r, $C$3:$H$4,
   hr, K$2, tr, $J3,
   a,  --(hr=LEFT(TRIM(h),5)),
   b,  a*SEQUENCE(1,COUNTA(h)),
   c,  FILTER(b,b),
   d,  INDEX(r, XMATCH(tr,t),c),
   e,  XMATCH(MAX(d),
   d), INDEX(h,INDEX(c,e))
)

[#5]

@mathetes 

Thank you for your reply and you are absolutely right! Sorry, for not replying earlier I had to work in between. I will add more details and a sample spreadsheet the next time I ask a question. Thank you for your response and help!

[#6]

@mtarler
Thank you I have just tried it and it works and it is much quicker, then most of my other formulas. Thank you!

[#7]

@Sergei Baklan
Thank you for your response and the file. I have never used a dynamic array before in Excel. This looks very interesting to use.
Thank you again!