Google-sheets – attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet

formulasgoogle sheetsgoogle-sheets-arrayformula

I found a proposed solution on Stack, but I can't get it to work.

https://webapps.stackexchange.com/q/120111/270308

Starting formula:

=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                               Sheet1!C1:C &" "&
                                               Sheet1!D1:D); Sheet1!A1:A=G3));)

I modified to match my Sheet's info, but I can't get to work.

I'm getting Filter range size errors:

–FILTER has mismatched range sizes.

–Expected row count: 1000. column count: 1.

–Actual row count: 1. column count: 1.

What I have noticed:

  • my formula refuses to maintain the ";" and autochanges them to ","
  • I have the added AND(), rather than a simple "="

My formula:

=IF(B4<>"",
JOIN(CHAR(10), FILTER(ARRAYFORMULA('Drive-Times'!A$3:A &"-"&
                                    'Drive-Times'!B$3:B), 
                                AND('Drive-Times'!D$3:D>=B4,'Drive-Times'!D$3:D<B5))),)

my sample Sheet

My problem cell is highlighted in Green: compilations!A4

Best Answer

Your setup of the formula is incorrect. FILTER can take any number of comma-separated arguments; it doesn't need the AND grouping. In addition, AND does not work within ArrayFormula anyway (and you didn't need ArrayFormula here, because FILTER is its own built-in array formula.

Try this:

=IF(B4<>"", JOIN(CHAR(10), FILTER('Drive-Times'!A$3:A & "-" & 'Drive-Times'!B$3:B, 'Drive-Times'!C$3:C>=B4, 'Drive-Times'!C$3:C<B5)),)

The use of semicolons in your template formula means that this person has a sheet that is set to a locale that uses semicolons in place of commas. Your sheet locale uses commas.

ADDENDUM (w/ IFERROR)

=IF(B4<>"", IFERROR(JOIN(CHAR(10), FILTER('Drive-Times'!A$3:A & "-" & 'Drive-Times'!B$3:B, 'Drive-Times'!C$3:C>=B4, 'Drive-Times'!C$3:C<B5)),"NONE"),)

Related Topic