0

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

Improve this question

1 Answer 1

Reset to default
0

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"),)

Improve this answer
4
  • It works great on my original as well! Thank you! Is there a way to mod it to return something else if no matches are found (eg. a blank "", or "NONE", or "n/a"? Commented Apr 20, 2021 at 0:18
  • See ADDENDUM to my post. If you would, please take a moment to complete the circle by marking that post "Best Answer." This indicates to contributors at a glance which issues have been fully resolved, as well as helping future site visitors like yourself most quickly find the answers to their own similar questions. Commented Apr 20, 2021 at 0:43
  • Sorry, imma noob. I clicked to Accept the answer, but I don't see "Best Answer" anywhere. Commented Apr 20, 2021 at 0:53
  • You must have figured it out, because it is showing now. Thanks. Commented Apr 20, 2021 at 0:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.