ExcelBanter - extracting cell location

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - extracting cell location (https://www.excelbanter.com/excel-discussion-misc-queries/2057-extracting-cell-location.html)

extracting cell location

How can you extract the cell value from the results of an equation? For
example, I would like to find the value of a formula like MAX(A4:A10). If
the results of that formula were located in Cell A5, I want it displayed.

I am trying to take the MAX of a row, and then find the corresponding cell
in the row below it. Example. MAX(A1:A10). Max cell is located in A5. I
want to display the results of B5 in the cell.

Any help is appreciated.

One way

=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))

Regards,

Peo Sjoblom

"Scott" wrote:

How can you extract the cell value from the results of an equation? For
example, I would like to find the value of a formula like MAX(A4:A10). If
the results of that formula were located in Cell A5, I want it displayed.

I am trying to take the MAX of a row, and then find the corresponding cell
in the row below it. Example. MAX(A1:A10). Max cell is located in A5. I
want to display the results of B5 in the cell.

Any help is appreciated.