Forum Discussion
Solved
How to keep the values of the cells if their influencing cells are being sorted?
Hello everyone! I have two sheets in Excel, Sheet1 and Sheet2. Since Sheet1 has too many columns, I created a separate sheet to combine different stats about Sheet1 in there. Thus, Sheet2 cells de...
Not sure on which platform/version of Excel you, as variant that could be
=MIN( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) ) =AVERAGE( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) )
If you don't see this option
you may share the file on OneDrive, GoogleDrive, whatever and share the link. Be sure proper permissions are set.
SergeiBaklan Okay, so here's the link to the table's fragment: https://1drv.ms/x/s!AkzHZFcHBpB8gppJEAGlxOGd6QJAJA?e=764Njh. Initially the file is way larger, but here's only the simple core which is enough for me to understand the solution's pattern. I would be quite thankful for any help!
Not sure on which platform/version of Excel you, as variant that could be
=MIN( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) ) =AVERAGE( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) )- Thank you very much! I explored the case a bit before you answered and found a similar solution with VLOOKUP:
=MIN(VLOOKUP(B2;Results!$B$4:$O$9;3;0);VLOOKUP(B2;Results!$B$4:$O$9;6;0);VLOOKUP(B2;Results!$B$4:$O$9;9;0);VLOOKUP(B2;Results!$B$4:$O$9;12;0)).
And then the same with the AVERAGE function.
Which solution do you think is more efficient?From performance point of view INDEX/MATCH usually is better.
If that's not critical use one with which you are most familiar, that's save time on maintenance.