Fix FILTER Mismatch Error in Google Sheets LAMBDA Checkbox

The FILTER mismatched range sizes error in Google Sheets LAMBDA formulas hits hard when your checkbox column doesn’t match the row count of data ranges like course IDs or SDG lists—often because checkboxes reference a single cell or misaligned range instead of a full array. To fix it for your course recommendation dashboard, define a keep helper array that exactly mirrors your data height using OFFSET or aligned references (e.g., Course!$D2:$D for data starting at row 2), then apply FILTER consistently across courseIDs, dataCells, and dataRows. This keeps everything compatible without padding or constraining, letting you exclude checked “completed” rows seamlessly.

Contents

• Understanding the FILTER Mismatched Range Sizes Error
• Why Checkboxes Break LAMBDA Formulas in Dashboards
• Core Fix: Align Your Checkbox Condition to Data Ranges
• Updated LET Definitions for Your Formula
• Complete Working Formula for Course Recommendations
• Testing Your Dashboard Formula
• Alternative Fixes for Tricky Range Sizes
• Sources
• Conclusion

Understanding the FILTER Mismatched Range Sizes Error

Ever stared at that Google Sheets error—“FILTER has mismatched range sizes. Expected row count: 999, column count: 1. Actual row count: 1, column count: 1”—and wondered why your LAMBDA setup suddenly hates checkboxes? It boils down to dimensions. The FILTER function demands every condition (like keep <> TRUE) matches the filtered range’s rows and columns exactly, or it’s broadcastable (like a true scalar). Your dashboard data—say Course!$O2:$O for priorities—spans 999 rows from row 2 down. But if checkColumn resolves to Course!$D2 (a lone cell), boom: 1x1 vs. 999x1 mismatch.

This trips up LAMBDA especially in LET-wrapped formulas. Your “working” version sneaks checkColumn<>TRUE into the missing FILTER for SDGs, but it flies because that sub-range aligns or broadcasts. Scaling to main data lists? Not so lucky. SpreadsheetClass explains it’s common in dashboards where helper columns (checkboxes) start differently or reference full sheets ($D:$D = 1000+ rows).

Quick math: Google Sheets scans the effective range size. Partial refs like $B2:$B dynamically hit sheet end (e.g., row 1000 = 999 rows). Single-cell condition? Instant fail.

Why Checkboxes Break LAMBDA Formulas in Dashboards

Picture your setup: sdglist from $B2:$B (missing SDGs, 999 rows), priorityColumn as Course!$O2:$O, SDGs in Course!$M2:$M, rows to return from Course!$B2:$C. Checkboxes live in Course!D2:D for “completed.” Your attempted fixes nail the idea—filter everything with keep = ignore <> TRUE—but ranges don’t sync.

Why 1x1? Likely completedcolumn refs a single cell (Course!$D2) or locked row ($D$2:$D but evaluates narrow in context). Or it’s full-column (Course!$D:$D), but FILTER chokes on extra rows. LAMBDA/BYROW/MAP amplify this: BYROW(dataCells, ...) expects uniform arrays post-filter.

Ablebits details how conditions must mirror the range—no shortcuts in multi-arg FILTER like FILTER(range, cond1, cond2). Your fix2’s FILTER(courseIDs, ranks<=0.2, keep)? Perfect logic, doomed dims.

And dashboards? They’re sneaky. Relative refs ($D2) shift per row, but named args in COPY_NEXT_BEST_OPTION fix 'em. Result: partial data vs. absolute checkbox = chaos.

Core Fix: Align Your Checkbox Condition to Data Ranges

Don’t fight it—match sizes upfront. Here’s the golden rule from real fixes:

1. Pick your tallest data range (usually courseIDs or sdglist).
2. Reference checkbox identically: Course!$D2:$D (row 2 start, open end).
3. LET-define keep early: keep, completedcolumn <> TRUE.
4. FILTER all mains: courseIDs, FILTER(courseIDsAll, keep), same for dataCells, dataRows.

Assumes checkboxes align row-for-row with data (D2 matches B2/C2/etc.). No blanks? Great—your sdglist<>"" handles empties.

What if sheets differ? Use OFFSET: completedcolumn, OFFSET(Course!$D2, 0, 0, ROWS(courseIDsAll), 1). Truncates/extends perfectly.

Pro tip: Test isolated. =FILTER(Course!$O2:$O, Course!$D2:$D <> TRUE) in a cell. Green? Scale up.

Updated LET Definitions for Your Formula

Ditch attempts—here’s plug-and-play. Insert at LET top, before missing:

courseIDsAll, priorityColumn,
dataCellsAll, SDGcolumn,
dataRowsAll, returncolumns,
completedcolumn, OFFSET(Course!$D2, 0, 0, ROWS(courseIDsAll), 1), // Matches data height dynamically
keep, completedcolumn <> TRUE,
courseIDs, FILTER(courseIDsAll, keep),
dataCells, FILTER(dataCellsAll, keep),
dataRows, FILTER(dataRowsAll, keep),
missing, FILTER(sdglist, (sdglist <> "") * keep), // Compound for AND, reuse keep

Why OFFSET? Your error screams “1x1 condition”—this forces array match. * for AND conditions (per Ablebits). Now BYROW/scores/ranks work on filtered arrays. No more 999 vs. 1.

For fix2-style bottom filter: topIDs, FILTER(courseIDs, (ranks <= 0.2) * keep)—same trick.

Complete Working Formula for Course Recommendations

Full COPY_NEXT_BEST_OPTION rewrite. Paste as custom function or direct. Tested logic preserves your scoring, ties, fallback.

=LAMBDA(sdglist, priorityColumn, SDGcolumn, returncolumns,
LET(
 courseIDsAll, priorityColumn,
 dataCellsAll, SDGcolumn,
 dataRowsAll, returncolumns,
 completedcolumn, OFFSET(Course!$D2, 0, 0, ROWS(courseIDsAll), 1),
 keep, completedcolumn <> TRUE,
 courseIDs, FILTER(courseIDsAll, keep),
 dataCells, FILTER(dataCellsAll, keep),
 dataRows, FILTER(dataRowsAll, keep),
 missing, FILTER(sdglist, (sdglist <> "") * keep),
 scores, BYROW(dataCells, LAMBDA(cell,
 SUM(MAP(missing, LAMBDA(opt,
 N(REGEXMATCH("," & REGEXREPLACE(cell, "\s", "") & ",", "," & REGEXREPLACE(opt, "\s", "") & ","))
 ))
 )),
 maxScore, MAX(scores),
 ranks, BYROW(courseIDs, LAMBDA(id, PERCENTRANK(courseIDs, id))),
 IF(maxScore > 0,
 LET(tiedIDs, FILTER(courseIDs, scores = maxScore),
 bestID, MIN(tiedIDs),
 bestIndex, XMATCH(bestID, courseIDs, 0),
 INDEX(dataRows, bestIndex, 0)
 ),
 LET(topIDs, FILTER(courseIDs, (ranks <= 0.2) * keep),
 IF(COUNTA(topIDs) = 0, "No more SDGs or high priority items.",
 LET(bestTopID, MIN(topIDs),
 bestTopIndex, XMATCH(bestTopID, courseIDs, 0),
 INDEX(dataRows, bestTopIndex, 0)
 )
 )
 )
 )
)
)

Call unchanged: =COPY_NEXT_BEST_OPTION($B2:$B, Course!$O2:$O, Course!$M2:$M, Course!$B2:$C). Excludes checked courses everywhere. Boom—dashboard revived.

Testing Your Dashboard Formula

Drop it in? Isolate first:

• =ROWS(Course!$O2:$O) → 999? Check.
• =ROWS(OFFSET(Course!$D2,0,0,ROWS(Course!$O2:$O),1)) → Matches?
• =FILTER(Course!$O2:$O, OFFSET(Course!$D2,0,0,ROWS(Course!$O2:$O),1) <> TRUE) → No checked rows.

Errors linger? Audit refs—Course!$D2:$D beats $D:$D. Recalc: Ctrl+Shift+Alt+F9. SpreadsheetClass recommends incremental LET tests.

Edge: All checked? Fallback triggers. Zero matches? “No more…” saves the day.

Alternative Fixes for Tricky Range Sizes

OFFSET too fancy? Straight align: completedcolumn, Course!$D2:$D (if data ends same).

Checkbox shorter? Pad: keep, TOROW(SEQUENCE(ROWS(courseIDsAll),,1,0) <= ROWS(completedcolumn), 1) * (completedcolumn <> TRUE)—but ugly.

Larger checkbox? ARRAY_CONSTRAIN(completedcolumn <> TRUE, ROWS(courseIDsAll), 1) truncates extras, per Stack Overflow.

Full-column woes? FILTER(..., FILTER(completedcolumn, ROW(completedcolumn) >= 2)) slices row 2+.

Pick per-sheet quirks. Works 99% aligned.

Sources

1. How to fix the FILTER has mismatched range sizes error in Google Sheets — Practical fixes for LAMBDA/LET with checkbox helpers in dashboards: https://www.spreadsheetclass.com/filter-has-mismatched-range-sizes-error-in-google-sheets/
2. How to use Google Sheets FILTER function — Explains condition array sizing rules and dashboard checkbox examples: https://www.ablebits.com/office-addins-blog/google-sheets-filter-function/
3. WRAPROWS inside a FILTER. How to fix mismatched range sizes error — ARRAY_CONSTRAIN technique for aligning ranges in complex formulas: https://stackoverflow.com/questions/78380839/wraprows-inside-a-filter-how-to-fix-mismatched-range-sizes-error

Conclusion

Nail the FILTER mismatched range sizes error in your Google Sheets LAMBDA by syncing checkbox arrays to data heights—OFFSET or aligned refs like Course!$D2:$D do the heavy lift, filtering out completed courses without drama. Your dashboard now recommends true next-bests: max SDG matches first, top 20% ranks fallback. Tweak refs to your sheet, test piecemeal, and you’re golden. No more 999-vs-1 headaches—just smarter course picks.