title: How to find first and last non-blank value in a table based on condition in Excel and convert it to text
body: <p>I have searched online but none of the answers over there seem to solve my problem.</p>
  
  <p>So basically I have a row (say A1:E2) withh the square feet </p>
  
  <pre><code>                                     Office Residential   Retail    Hotel   
   Investment Period (start/end)        1y/3y  1y/2y        1y/2y     1y/2y
  </code></pre>
  
  <p>And then in another range (A3:G8) a table built like that:</p>
  
  <pre><code>          Year 1         Year 1      Year 2      Year 2      Year 3     Year 3
            1st Sem.       2nd Sem.    1st Sem.   2nd Sem.    1st Sem.    2nd Sem.
  Office      25%            25%                    25%                    25%    
  Residetial  35%            35%                    30%       
  Retail      20%            20%         20%        40%
  Hotel       20%            20%         20%        40%
  </code></pre>
  
  <p>Now, in B2:E2 I have personally put the text like 1y/2y, 2y/3y and so on. Instead, i would like some kinfd of index match formula (or lookup) that does it for me, since the table is much bigger than that. Is there anything that could help me? </p>
  
  <p>I have tried (in B2) something like =INDEX($A3$:$G$8,MATCH(B2,INDEX($A3$1:$A$8&lt;>"",0),0)) but it does not work properly... and then there is the problem of converting the value found in the corresponding year start and year end.</p>
  
  <p>I hope I was exaustive! Thanks</p>