Google Sheets：含标题的非空数组堆叠实现问题咨询

Hey Diego, let's sort out this array stacking issue in Google Sheets once and for all! The key challenge here is stacking only the non-empty arrays (including their headers) without extra blank rows, and avoiding those annoying errors from misusing IF.

Here are two solid solutions that fix your problem:

Solution 1: Use LET + VSTACK + FILTER with INDIRECT (matches your original approach) #

This formula builds on your existing INDIRECT logic but adds safeguards to skip empty arrays and clean up blank rows:

=LET(
  stacked_data, VSTACK(
    IF(D3>0, INDIRECT("B5:D"&D3+5), ),
    IF(H3>0, INDIRECT("F4:H"&H3+4), ),
    IF(L3>0, INDIRECT("J4:L"&L3+4), ),
    IF(P3>0, INDIRECT("N4:P"&P3+4), )
  ),
  FILTER(stacked_data, NOT(ISBLANK(INDEX(stacked_data,,1))))
)

How it works: #

• LET creates a named variable (stacked_data) to avoid repeating the same VSTACK calculation, making the formula faster and easier to read.
• Each IF checks if the array has data (e.g., D3>0). If yes, it uses INDIRECT to pull the full range including the header. If no, it returns an empty array (note the trailing comma instead of ""—this avoids type mismatch errors).
• VSTACK stacks all valid arrays (and empty placeholders) together.
• FILTER removes any blank rows by checking if the first column of the stacked data is not empty.

Solution 2: Use OFFSET instead of INDIRECT (more robust) #

INDIRECT relies on cell address text, which can break if you rearrange columns/rows. OFFSET is dynamic and safer for ranges that change size:

=LET(
  arr1, IF(D3>0, OFFSET(B5, 0, 0, D3+1, 3), ),
  arr2, IF(H3>0, OFFSET(F4, 0, 0, H3+1, 3), ),
  arr3, IF(L3>0, OFFSET(J4, 0, 0, L3+1, 3), ),
  arr4, IF(P3>0, OFFSET(N4, 0, 0, P3+1, 3), ),
  combined, VSTACK(arr1, arr2, arr3, arr4),
  FILTER(combined, NOT(ISBLANK(INDEX(combined,,1))))
)

How it works: #

• OFFSET(start_cell, row_offset, col_offset, total_rows, total_cols): For each array, we start at the header's first cell, set offsets to 0, and calculate total rows as data_count + 1 (to include the header). The 3 is for the 3 columns in each array.
• The rest of the logic mirrors Solution 1: stack valid arrays, then filter out blank rows.

Why your earlier IF("") approach failed: #

Returning "" (a text value) instead of an empty array causes a type mismatch when VSTACK tries to combine text with array ranges. Using an empty placeholder (the trailing comma) tells Google Sheets to skip that entry entirely.

Both solutions will only stack arrays that have data, include their headers, and avoid extra blank rows—exactly what you need!

内容的提问来源于stack exchange，提问作者Diego Robles