Forum Discussion
Finding Location of Maximum Value
I have two tabs in my worksheet MeterData and PeakLoad
I am trying to locate the maximum value from an array in MeterData tab, cells F2:CW733. I also need to find the date corresponding to the maximum value. The dates are listed in Column D.
I need to put this data into the PeakLoad tab. I located the maximum value from my first tab using the MAX function. I am unable to locate the coordinates of this cell using MATCH or INDEX.
I used the following formula: =INDEX('MeterData'!F2:CW733, MATCH(MAX('MeterData'!F2:CW733), 'MeterData'!D2:D733, 0), 1). I get a N/A as the result.
Please help. Thank you.
2 Replies
Formula to find max location in 2D range is here https://exceljet.net/formula/get-location-of-value-in-2d-array
KiranPK The problem is that match is for a 1-d array and you are giving it a 2-d array. I would suggest you use a helper column to find the max value on the row and then you can find the max row in the helper column and the max in the row to find your max cell but it would be cleaner if you also have a helper row to find which column has the max value.
