Find a maximum value by one value that belongs to multiple values

[CoenH]

Hello everyone,

I am not so sure on how to make my question clear in the title, so let me explain:

My input is a "Grp" number in cell B3. In this case this number is "0".
Now I would like to know what "node1" numbers belong to "Grp 0" . This must be done in the red box.
Now we know what "node1" numbers belong to "Grp 0", I would like to have the maximum value of u-X that belong the node1 numbers from "Grp" (Red box)
The result should be 0.979. Is there any way to this in one formula?

Excel question.png


Any help is greatly appreciated!

[Bo_Ry]

Please try

=MAX(INDEX(SUMIFS(H11:H30,G11:G30,B11:B30)*(C11:C30=B3),))

[davsth]

can you have helper columns? it can be done but is messy. How many rows in your real data?

[CoenH]

Davsth,

No, because my data is exported from a calculation program and it cannot be sorted.
Real data contains a minimum of 150.000 rows..

Looks like Bo_Ry's solution is working, let me check on it.

[CoenH]

@Bo_Ry,

Thanks, it looks like it is working. How can I change this formula to search the minimum value instead of the maximum?
Changing MAX to MIN does not seem to work

[Bo_Ry]

Min
=AGGREGATE(15,6,VLOOKUP(N(INDEX(+B11:B30,)),G11:H30,2,0)/(C11:C30=B3),1)

[CoenH]

Thank you!

[T.I.]

One more variant:
Max: =AGGREGATE(14;6;10^LOG(SUMIFS(H11:H30;G11:G30;B11:B30)*(C11:C30=B3));1)
Min: =AGGREGATE(15;6;10^LOG(SUMIFS(H11:H30;G11:G30;B11:B30)*(C11:C30=B3));1)