First column value and upper row differences

Question

I have two questions

1) How is it possible to get the text of the first column of a matrix, depending on the position of the max value in a column? I tried all kinds of different formulas but can't seem to get this.

2) How can I with formulas determine the difference between the upper and the upper but one row? For the first column I need a difference of 5, for the second 2, for the third 9. I could do this with something like =if(C1<>"";C1;B1), but there could be gaps of 50 cells in the first row of my matrix. If this isn't possible, I'll solve this with VBA.

Answer 1

^{1) How is it possible to get the text of the first column of a matrix, depending on the position of the max value in a column? I tried all kinds of different formulas but can't seem to get this.}

In C13 as a standard formula,

=INDEX($B4:$B10, MATCH(MAX(C4:C10), C4:C10, 0))

      

^{2) How can I with formulas determine the difference between the upper and the upper but one row? For the first column I need a difference of 5, for the second 2, for the third 9. I could do this with something like =if(C1<>"";C1;B1), but there could be gaps of 50 cells in the first row of my matrix. If this isn't possible, I'll solve this with VBA.}

In C14 as a standard formula,

=ABS(INDEX(2:2, MATCH(1E+99, $A2:C2))-INDEX(3:3, MATCH(1E+99, $A3:C3)))