Excel INDEX MATCH formula for multiple rows
<p>I have hit a problem with my Excel spreadsheet and am hoping for some guidance. I have a spreadsheet with multiple tabs, and in the "Matrix" tab I am trying to get cell <code>B2</code> to Index the data <code>'Matrix Patch' Row 1:1</code> if there is a "1" in any cell in <code>Rows 72:191</code>. The current formula I am using is:</p>

<pre><code>=INDEX('Matrix Patch'!1:1,MATCH(1,'Matrix Patch'!72:191,0))
</code></pre>

<p>The answer I get is always N/A. </p>

<p>I can get it to work if I use:</p>

<pre><code>=INDEX('Matrix Patch'!1:1,MATCH(1,'Matrix Patch'!72:72,0))
</code></pre>

<p>but that only works if there is a "1" in Row 72.</p>

<p>Any suggestions? </p>