 # Using a cell reference indirectly in a formula

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.

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.

Any help is much appreciated!

Thanks

6 Replies

# Re: Using a cell reference indirectly in a formula

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.

# Re: Using a cell reference indirectly in a formula

@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))``

# Re: Using a cell reference indirectly in a formula

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))
)``````