title: How to make a relative reference in an array formula in Google Sheets
body: <p>Here's the straightforward version of my question:</p>
  
  <p>I want to change the following formula to an array formula...</p>
  
  <p>Original formula (from cell J2):  </p>
  
  <pre><code>=if(F4="VM:",G4,J1)
  </code></pre>
  
  <p>My attempt at converting to an array formula (in cell K1):</p>
  
  <pre><code>=arrayformula(if(row(A:A)=1,G3,if(F:F = "VM:",G:G,indirect("K"&amp;row(A:A)-1))))
  </code></pre>
  
  <p>This works on rows where F = "VM:", but returns a #REF error on other rows.  Function INDIRECT parameter 1 value is 'K0'. It is not a valid cell/range reference.</p>
  
  <p>Thoughts on how to fix this?</p>
  
  <hr>
  
  <p>The more complex version of my question.  i.e. Why am I trying to do this?...</p>
  
  <p>I have a weird spreadsheet with data that should really be in a Wiki.  </p>
  
  <p>I want to create filter views for each person so they can easily filter on only their own vendors.  The original formula will work, but as more vendors are added, I'd like for the formula to automatically work for those rows as well.</p>
  
  <p>If there's a better way to do this, I'm listening.</p>
  
  <p><a href="https://i.stack.imgur.com/p65BR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/p65BR.png" alt="enter image description here"></a></p>