获取其他Google Sheet列最后非空单元格时遇FILTER错误求助

Hey there! Let's break down why you're hitting that "mismatched range sizes" error when trying to grab the last non-empty cell from an imported column via IMPORTRANGE, and how to fix it.

Why the Error Pops Up #

The error message tells you it expected a 1x1 range but got 1 row with 563 columns—this usually happens because either:

• Your IMPORTRANGE is accidentally pulling multiple columns instead of the single column you need, or
• The range used in your FILTER condition doesn't align with the dimensions of the imported data.

Unlike local sheet data (where you might be working with a tightly defined range), IMPORTRANGE often returns the entire column (including thousands of empty rows at the bottom), which can throw off FILTER's expected range size.

Reliable Solutions to Get the Last Non-Empty Cell #

Skip the FILTER headache with these two straightforward methods:

Method 1: INDEX + COUNTA (for columns with no gaps)

If your target column has no blank cells between data points, this formula works perfectly:

=INDEX(IMPORTRANGE("your-spreadsheet-url", "TargetSheet!C:C"), COUNTA(IMPORTRANGE("your-spreadsheet-url", "TargetSheet!C:C")), 1)

• Swap "your-spreadsheet-url" with the link to your external sheet.
• Replace "TargetSheet!C:C" with the exact column you want to pull (e.g., "SalesData!A:A").
• COUNTA counts all non-empty cells in the imported column, and INDEX grabs the value at that row number.

Method 2: LOOKUP (works even with blank cells)

If your column has scattered empty cells, LOOKUP is more reliable—it skips errors and finds the very last non-empty value:

=LOOKUP(2, 1/(IMPORTRANGE("your-spreadsheet-url", "TargetSheet!C:C")<>""), IMPORTRANGE("your-spreadsheet-url", "TargetSheet!C:C"))

• How it works: 1/(IMPORTRANGE(...)<>"") turns non-empty cells into 1 and empty cells into #DIV/0! errors. LOOKUP ignores these errors and finds the last occurrence of 1, returning the corresponding cell value.

Quick Tip to Avoid Future Errors #

Always double-check your IMPORTRANGE range: make sure you're specifying a single column (like C:C) instead of a wide range (like A:ZZ). If you still want to use FILTER, ensure your condition range matches the imported data's dimensions exactly (same number of rows/columns).

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