Concatenate two ranges in Google Sheets
<p><strong>tl;dr</strong>: How do I make two ranges appear as one as an argument to a function? For example, I want to look at every cell in column E <strong>except</strong> E5:<br>
<code>=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )</code></p>

<p><strong>Background</strong><br>
I'm writing a project tracker in Google Sheets, like Microsoft Project. I have a <a href="https://stackoverflow.com/q/35509684/405017">custom function in Google Sheets</a> that allows me to calculate the start date for various tasks by looking at the maximum end date of all tasks that it depends upon:</p>

<pre><code>=MAX_LOOKUP( G9, A:A, I:I )
</code></pre>

<p>However, the end date for this task is then calculated based on the start date, and that end date is in the column with all end dates that are examined. As a result, I have a circular dependency.
<a href="https://i.stack.imgur.com/QgOpR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QgOpR.png" alt="enter image description here"></a></p>

<p>So, I'd like to write my formula for each line to skip over the current line, like:</p>

<pre><code>=MAX_LOOKUP( G9, A1:A8 + A10:A, I1:I8 + I10:I )
</code></pre>

<hr>

<p><em>FWIW my current workaround is to rewrite the custom function's signature like so:</em></p>

<pre><code>=MAX_LOOKUP_SKIPROW( G9, ROW(), "A", "I" )
</code></pre>

<p><em>and manually construct the two ranges within that function using:</em></p>

<pre class="lang-js prettyprint-override"><code>var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var keys1 = sheet.getRange(keyCol+"1:"+keyCol+(skipRow-1)).getValues();
var vals1 = sheet.getRange(valCol+"1:"+valCol+(skipRow-1)).getValues();

var keys2 = sheet.getRange(keyCol+(skipRow+1)+":"+keyCol).getValues();
var vals2 = sheet.getRange(valCol+(skipRow+1)+":"+valCol).getValues();
</code></pre>

<p><em>This is a functional solution, but makes the function more special purpose. I do not consider this to be an "answer" to this more general question of joining ranges.</em></p>