title: Excel: Find intersection of a row and a column
body: <p>My question is how can I find an intersecting cell of a specific column and row number?</p>
  
  <p>My situation is this: with some calculations I find two cells, lets say <strong>B6</strong> and <strong>E1</strong>. I know that I need a row of the first one and a column of the second one. So I could just use <strong>ROW</strong> and <strong>COLUMN</strong> functions to get the numbers. After that, I need to find an intersecting cell. Which would be <strong>E6</strong> in this example.</p>
  
  <p>I would just use <strong>INDEX(A1:Z100;ROW;COLUMN)</strong> but I don't know the exact area that I'm going to need - it depends on other stuff. I could use something like <strong>A1:XFG65000</strong> but that is way too lame. I could also use a combination of <strong>INDIRECT(ADDRESS())</strong> but I'm pulling data from a closed workbook so <strong>INDIRECT</strong> will not work.</p>
  
  <p>If this would help to know what is this all for - here's a concrete example:
  I need to find limits of a section of a sheet that I would work with. I know that it starts from the column <strong>B</strong> and goes all the way down to the last non-empty cell in this column. This range ends with a last column that has any value in <strong>first row</strong>. So to define it - I need to find the intersection of this last column and the last row with values in B column.
  I use this array formula to find the last column:</p>
  
  <p><strong>INDEX(1:1;MAX((1:1&lt;>"")*(COLUMN(1:1))))</strong></p>
  
  <p>And this array formula to find the last row:</p>
  
  <p><strong>INDEX(B:B;MAX((B:B&lt;>"")*(ROW(B:B)))</strong></p>
  
  <p>Last column results in <strong>E1</strong> and last row results in <strong>B6</strong>. Now I need to define my range as <strong>B1:E6</strong>, how can I get <strong>E6</strong> out of this all to put into the resulting formula? I've been thinking for a while now and not being and Excel expert - I couldn't come up with anything. So any help would really be appreciated. Thanks!</p>