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

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

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"? Apr 20 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.
    – Erik Tyler
    Apr 20 at 0:43
  • Sorry, imma noob. I clicked to Accept the answer, but I don't see "Best Answer" anywhere. Apr 20 at 0:53
  • You must have figured it out, because it is showing now. Thanks.
    – Erik Tyler
    Apr 20 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.