Return the Greatest Value in a Range

[maic15]

Hello,

I have a issue that I hope some can help me. I need to tax the max value of the "# of Unique providers for a durations" field and return the value from "Provider-Level Duration" in a seperate column.

I have three columns with three records:
ID:
Provider-Level Duration:
# of Unique providers for a durations:

The values in the field are the following:
ID:
CAR INST FOR DEV DISABILITIESNEW MEDICAL CONSULTATION15
CAR INST FOR DEV DISABILITIESNEW MEDICAL CONSULTATION180
CAR INST FOR DEV DISABILITIESNEW MEDICAL CONSULTATION120
Provider-Level Duration:
9
1
10
# of Unique providers for a durations:
15
180
120

I'm looking for the following result:
ID: CAR INST FOR DEV DISABILITIESNEW MEDICAL CONSULTATION120
Provider-Level Duration: 10 (This is the max value of the three records
# of Unique providers for a durations:120

 

[LarryLegend]

If I understand correctly, you want to look for the maximum value in Column B (Provider-Level Duration) and then based on that, pull the corresponding information from Column A (ID) and Column C: (# of Unique providers for a durations) from the same row as your match in Column B. Is that correct?

If so, can you also let me know if the values in Column B unique? For example, is there a case where multiple records might have the same maximum value for Column B (Provider-Level Duration) ?

 

[maic15]

Correct. Column A (ID) will have unquie values. I mistyped "CAR INST FOR DEV DISABILITIESNEW MEDICAL CONSULTATION"

 

[LarryLegend]

To determine the maximum value in Column B, you can use this formula in cell D2: =max(b:b)

To then pull in the corresponding value from Column A: =INDEX(A:A,MATCH(D2,B:B,FALSE),1)
To then pull in the corresponding value from Column C: =INDEX(C:C,MATCH(D2,B:B,FALSE),1)

 

[maic15]

I think I stated it wrong. I need a 1 enter into a cell in column D that matches my logic.

For example, in an array A2:A4, take the max of C2:C4, and return a value of B2:B4, that matches the max of C2:C4

 

[LarryLegend]

If I understand correctly, you are looking for the greatest value in Column C and then pulling the corresponding data in Column B from the same row that the max C value was found. If you want it all in a single cell you could do =INDEX(B:B,MATCH(MAX(C:C),C:C,FALSE),1)

 

[Aladin Akyurek]

I think I stated it wrong. I need a 1 enter into a cell in column D that matches my logic.

For example, in an array A2:A4, take the max of C2:C4, and return a value of B2:B4, that matches the max of C2:C4

And that value is?