title: Apply VlLOOKUP till last non-empty row in google sheets
body: <p>I have an automatically expanding google in which I am applying a Vlookup formula to extract values from another sheet matching the values from the main sheet.</p>
  
  <p>This is the Vlookup formula-</p>
  
  <pre><code>=VLOOKUP(A2, IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"),4,0)
  </code></pre>
  
  <p>I want the formula to be auto applied to the new entries which get auto added in the main sheet. I used this formula I found on a website to apply this formula to the whole column also taking care of any empty cells in between.</p>
  
  <pre><code>=ArrayFormula(IF(ISBLANK(A2:A), "", VLOOKUP(A2:A, IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"),4,0)))
  </code></pre>
  
  <p>My problem is that after using this formula when a new entry is auto added to column A in the sheet, it gets added to 1001th row instead of the next non-empty row, because this formula is being applied to whole column.</p>
  
  <p>Is there some way to apply the Vlookup to just be applied till last non-empty row, so that my next automatic entry gets added in the next empty row? Or any other alternative solution? 
  Thanks for the help!</p>