title: Formula to get last value in columns then value of another cell in the same row
body: <p>I have a table with rows = <code>depth</code> and columns = <code>value</code> of the surveys done in each month and year. Not all years/months have surveys so some cells are blank. What I want to do is to determine to which depth each survey has been done, because it is not the same for all years/months. The <code>depth</code> values are stored in Column E and the survey <code>value</code>s are from Column F to DM.</p>
  
  <p>I was able to call the last value in each column using this formula from <a href="http://www.cpearson.com/excel/LastValueInRowOrColumn.aspx" rel="nofollow noreferrer">Pearson</a>: </p>
  
  <pre><code>=INDIRECT(ADDRESS(MAX((F1:F111&lt;&gt;"")*(ROW(F1:F111))),COLUMN(F:F)))
  </code></pre>
  
  <p>but this gives me <code>value</code> (which I don't need), not <code>depth</code>.</p>
  
  <p>I want to know which <code>depth</code> (row value) this last <code>value</code> corresponds to.</p>