I have at table set up with a column sorted max to min in a column. There
can be more than one entry with the same max value. I need to find the min
and max in other columns based on the max in the first column.

5 80.6 <- max value of interest
5 36.1 <- min value of interest
5 53
4 95.1
4.2 23.4
3 87

The table is bigger and I need to pick out multiple values for the max in
the first column. This function will get the first value of the max in the
first column. It returns the 80.6.

=INDEX(R1C2:R6C2,MATCH(MAX(C1),R1C1:R6C1,0),1)

Thanks for any pointers.

Hi,

Try these array formula

=MAX(IF(A1:A10=5,B1:B10))
=MIN(IF(A1:A10=5,B1:B10))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"ejack" wrote:

I have at table set up with a column sorted max to min in a column. There
can be more than one entry with the same max value. I need to find the min
and max in other columns based on the max in the first column.

5 80.6 <- max value of interest
5 36.1 <- min value of interest
5 53
4 95.1
4.2 23.4
3 87

The table is bigger and I need to pick out multiple values for the max in
the first column. This function will get the first value of the max in the
first column. It returns the 80.6.

=INDEX(R1C2:R6C2,MATCH(MAX(C1),R1C1:R6C1,0),1)

Thanks for any pointers.

I misread your post try these


For the MIN base on the max in column A
=MIN(IF(A1:A10=MAX(A1:A10),B1:B10))

For the max based on the max in column A
=MAX(IF(A1:A10=MAX(A1:A10),B1:B10))


Mike
"Mike H" wrote:

Hi,

Try these array formula

=MAX(IF(A1:A10=5,B1:B10))
=MIN(IF(A1:A10=5,B1:B10))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"ejack" wrote:

I have at table set up with a column sorted max to min in a column. There
can be more than one entry with the same max value. I need to find the min
and max in other columns based on the max in the first column.

5 80.6 <- max value of interest
5 36.1 <- min value of interest
5 53
4 95.1
4.2 23.4
3 87

The table is bigger and I need to pick out multiple values for the max in
the first column. This function will get the first value of the max in the
first column. It returns the 80.6.

=INDEX(R1C2:R6C2,MATCH(MAX(C1),R1C1:R6C1,0),1)

Thanks for any pointers.

OK, Thanks Mike, that works for my test case, it will take some time to check
it on the actual data. Thanks again!

"Mike H" wrote:

Hi,

Try these array formula

=MAX(IF(A1:A10=5,B1:B10))
=MIN(IF(A1:A10=5,B1:B10))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"ejack" wrote:

I have at table set up with a column sorted max to min in a column. There
can be more than one entry with the same max value. I need to find the min
and max in other columns based on the max in the first column.

5 80.6 <- max value of interest
5 36.1 <- min value of interest
5 53
4 95.1
4.2 23.4
3 87

The table is bigger and I need to pick out multiple values for the max in
the first column. This function will get the first value of the max in the
first column. It returns the 80.6.

=INDEX(R1C2:R6C2,MATCH(MAX(C1),R1C1:R6C1,0),1)

Thanks for any pointers.