# Use of Index/Match/MaxIf not returning number associated with the maximum value

#### ccap

##### New Member
Hi All,

Please forgive me in advance, I am new to using forums and explaining my thoughts in terms of Excel jargon. I have a fairly large data set (141,884 rows x 4 columns) of oceanography data collected from an ongoing study from 2007-2011. The columns include: CRUISE ID (boat trip identification #), DEPTH (how far down the instrument was lowered into water), TEMP (water temperature at specified depth), and CHANGE (change in water temperature from previous measurement). An example is shown below of how the data are set up. The data are presented as increasing depth until the next cruise takes place.

I am attempting to find the thermocline (depth at which water temperature decreases rapidly) of the water for each cruise (n = 993). I am defining the thermocline at the depth (Column B) where the temperature change (Column D) is the greatest positive value for each cruise (Column A). In a Column F, I have listed each of the 993 cruises by their ID number and used the following formula in Column G: =INDEX(B:B, MATCH(MAX(IF(A:A=F2,D:D,0)),D:D,0)) and hit Ctrl+Shift+Enter afterwards. While the formula retrieves a number of depths associated with the maximum temperature change, I have several depths that are not correlated with the max value. Any ideas?

 A B C D E F G 1 CRUISE DEPTH TEMP CHANGE CRUISE ID THERMOCLINE 2 1021901 7 20.1737 0 1021901 3 1021901 8.9 20.1769 -0.0032 1021902 4 1021901 10.9 20.1798 -0.0029 1021903 5 1021901 12.9 20.1809 -0.0011 1021904

<tbody>
</tbody>

Thanks,

Connor

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Replies
0
Views
1K
Replies
4
Views
1K

1,195,848
Messages
6,011,948
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back