3

I'm using the FILTER function to return a short list of items from a separate sheet.

This is my current formula in B2:

=FILTER('In Care'!D2:D999, A2='In Care'!J2:J999)

The results spill into the rows below the formula as long as they are empty or return an error if there are not enough empty rows below the formula to accommodate all the results:

#REF!
Array result was not expanded because it would overwrite data in [A1].

I've been leaving empty rows below each formula to accommodate results however the number of values returned varies and I don't want to leave unnecessary empty rows between the formulas.

What I'd like to do instead of returning multiple rows, is to return a single cell with multiple lines. In this way, I can have formulas stacked one row after the other without leaving empty rows in between to accommodate their results.

How do I do this?

Current Results

A B                                                     
 1  Lookup Value  Formulas 
2 Val_01 Result_1  ← Formula
3 Result_2
4 Result_3
5  ←  Not Needed
6  ←  Not Needed
7 Val_02 Result_1  ← Formula
8 Result_2
9 Result_3
10 Result_4
11  ←  Not Needed

Desired Results

A B                                                     
 1  Lookup Value  Formulas 
2 Val_01 Result_1
Result_2
Result_3
 ← Formula
3 Val_02 Result_1
Result_2
Result_3
Result_4
 ← Formula
2
  • 1
    Your edit clarifies things well, and improves the question. Thank you. I now know a bit more about SO markdown, too. :)
    – Graham
    Mar 19 at 22:03
  • Exactly how I learn everything too 👍
    – Blindspots
    Mar 19 at 22:13

1 Answer 1

2

TL;DR

Use the TEXTJOIN Function

BYROW  CHAR  FILTER  IFNA  TEXTJOIN  LAMBDA


Formulas

  1. Single Row versions:
    • must be pasted individually into each new row of Column A (1 formula for each row with a value)
    • must have absolute references to row numbers in 'In Care' to stop them from changing when you move the formula between rows.
  2. Multi-Row versions:
    • need only be entered once and will dynamically accept new lookup values added to Column A as well as any changes made to the data in sheet 'In Care' Columns D and J.
    • range references can be relative as the formula does not move rows.
  3. Both versions have been broken out into multiple rows for legibility. You can convert them to a single line by removing the breaks and spaces if you prefer.

1. Comma Delimited

If you prefer a space after each comma use ", " instead of ","

# Single Row 
=IFNA(
   TEXTJOIN(",", 1,
     FILTER('In Care'!D$2:D, 'In Care'!J$2:J=A2)))

# Multi-Row Formula
=BYROW(A2:A, LAMBDA(r,  
   IFNA(
     TEXTJOIN(",", 1, 
       FILTER('In Care'!D2:D, 'In Care'!J2:J=r)))))

2. Line Break Delimited

# Single Row Formula 
=IFNA(
   TEXTJOIN(CHAR(10), 1,
     FILTER('In Care'!D$2:D, 'In Care'!J$2:J=A2)))  

# Multi-Row Formula
=BYROW(A2:A, LAMBDA(r,  
   IFNA(
     TEXTJOIN(CHAR(10), 1, 
       FILTER('In Care'!D2:D, 'In Care'!J2:J=r)))))

3. Transpose Values to Rows

This alternative transposes the results so they stack.

# Single Row Formula 
=IFNA(
   TOROW( 
     FILTER('In Care'!D$2:D, 'In Care'!J$2:J=A2))) 

# Multi-Row Formula
=BYROW(A2:A, LAMBDA(r, IF(LEN(r), 
   IFNA(
     TOROW( 
       FILTER('In Care'!D$2:D, 'In Care'!J$2:J=r))),)))
5
  • 1
    Thanks for your reply. This is almost working for me. I tried your first solution, with commas, and I get the 1 cell I want .. plus rows of commas until end of the sheet (or a #REF telling me I need more space). If I add another condition to the FILTER, like 'In Care'!J2:J<>""I get #N/A to the end of the document, telling me that no matches are found. Am I doing something obviously wrong? Should I make a minimal example and add it to my question?
    – Graham
    Mar 17 at 19:49
  • Sorry hadn't finished. Added handling for no matches and empty rows in column A. Try it now.
    – Blindspots
    Mar 17 at 22:07
  • 1
    Try textjoin(). Mar 17 at 22:26
  • @doubleunary of course doh! Updated. Thank you! (as usual!)
    – Blindspots
    Mar 17 at 23:37
  • 1
    Got it. Thank you for the multiple options!
    – Graham
    Mar 19 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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