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} ) )
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} ) )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.
- 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?