MAX, IF statements?

[Guest]

I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

 

[RagDyer]

Try this *array* formula:

=INDEX(E1:E5,MATCH(MAX((G1:G5>=48)*(J1:J5)),J1:J5,0))

 

[Guest]

That works except when two values in column J are equally the largest. In
that case, the formula ignores the > 48 qualifier and displays the name that
corresponds to the first of the largest values.

Another issue is that in some cases I need to display the name based on the
lowest value in column J, but when I substitute MIN for MAX, the formula
again ignores the > 48 qualifier and goes to the first lowest value in the
column.

 

[Guest]

Try this:

=INDEX(E1:E5,MATCH(MAX(IF(G1:G5>=48,J1:J5)),J1:J5,0))

ctrl>shift>enter (not just enter)

 

[Guest]

One way to get it up in descending & ascending order
which caters for the possibility of ties/multiple ties in the col J values

---- "MAX" -------
Auto-listing names from col E in descending order by col J values
(only for names with col G values >=48)

In K1:
=IF(G1="","",IF(G1>=48,J1-ROW()/10^10,""))

In L1:
=IF(ROW()>COUNT(K:K),"",INDEX(E:E,MATCH(LARGE(K:K,ROW()),K:K,0)))

Select K1:L1, fill down to cover the max expected extent of source data.
Hide away col K. Col L returns the full list of names, neatly bunched at the
top. Names with tied col J values will be listed in the same relative order
that they appear within the source data.

------- "MIN" -------
Auto-listing names from col E in ascending order by col J values
(only for names with col G values >=48)

In N1:
=IF(G1="","",IF(G1>=48,J1+ROW()/10^10,""))

In O1:
=IF(ROW()>COUNT(N:N),"",INDEX(E:E,MATCH(SMALL(N:N,ROW()),N:N,0)))

Select N1:O1, fill down to cover the max expected extent of source data.
Hide away col N. Col O returns the full list of names, neatly bunched at the
top. Names with tied col J values will be listed in the same relative order
that they appear within the source data.

 

[Biff]

What about the column G value?

Shouldn't someone with a higher column G value be listed first if they also
have the highest column J value?

Column E Column G Column J
Bell, Charlie 48 .450
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

Biff

 

[Max]

Col G's values were used simply as pre-qualifiers, from my interp on the
OP's:

.. a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E

---

 

[Guest]

=INDEX( E1:E5, MATCH( MAX( IF( G1:G5 >= 48, J1:J5 ) ), IF( G1:G5 >= 48, J1:J5
), 0 ) )

 

[Guest]

Sorry but this ignores the "> 48" qualifier and just lists the first of the
two highest values that are equal. Thank you anyway.

Bob

 

[Max]

Bob,

I've suggested a non-array way to handle ties / multiple ties in response to
your feedback to RD in the other branch. Let me know how it went for you.

---