Feb 17 2021 05:43 AM
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
Feb 17 2021 02:31 PM
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.
Feb 17 2021 03:11 PM
@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))
Feb 18 2021 04:45 AM - edited Feb 18 2021 04:46 AM
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))
)
Feb 18 2021 02:15 PM
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!
Feb 18 2021 02:19 PM
Feb 18 2021 02:22 PM