Cell relative to current row in an array formula

Question

I've got a spreadsheet listing items with a possible child-parent relation (no grand children, children never come before their parent, but children of different parents can be mixed), and I'd like to list next to each parent its family list with the parent and its children (column C):

	A	B	C	D
1	Item	Parent if applicable	Expected: family photo	`{=IF(B2:B8="",A2:A8&","&TEXTJOIN(",",1,IF(B2:B8=A2,A2:A8,"")),"")}`
2	Fruit		Fruit,Apple,Banana	Fruit,Apple,Banana
3	Apple	Fruit
4	Animal		Animal,Pangolin,Duck	Animal,Apple,Banana
5	Pangolin	Animal
6	Duck	Animal
7	Banana	Fruit
8	Thing		Thing	Thing,Apple,Banana

N.B.: ported from a French LibreOffice with translated function names, and ;s instead of ,s, so the formula may have translation typos

But I don't understand how (or if?) I can refer to the current line of the array to get "the A column of every line whose B matches the current line's A";
I was expecting that using =A2 in cell D2 would mean "3 columns left from the current cell", but no way, as seen in column D hereabove, it's as if I had written =A$2 and I get fruits all over the column (OK still that's less invasive and healthier than an army of pangolins, ducks, and things, but I'm getting away from my goal)

What is the standard, simple way to refer to a cell relative to the current's one in an array formula?

Other attempts:

Although {=OFFSET(A2,ROW()-2,0)} (seen in a Google Spreadsheet question) works as a standalone array formula, for example in column E, integrating it in the full formula (…,IF(B$2:B$8=OFFSET(A2,ROW()-2,0),A$2:A$8,"")…) only returns blank cells
using a non-array formula (replicated to the subsequent lines) fails (blank cells too)
I ended up creating 1 dedicated array formula per cell but doesn't it defeat the purpose of array formulas?

(LibreOffice 25.2.2.2)

Bonus question

If a solution can be found for at least getting this list of children,
then a hint on prefixing with the head of family (Fruit, or Animal,) would be welcome.

The simplest way (concatenating it at the start with a ",") lets a trailing , when the entry has no child in its family (Thing,),
so for now I have a dirty solution that then REGEX(A2&","&TEXTJOIN(…),",$","").

But I suspect it would be possible to make the family head enter the IF with an OR ("take column A of the array when (array row's column B equals current row's A) OR (array row is the current row)").

I don't see why you showed that as the example formula. Doing =IF(B2:B8="",A2:A8&","&TEXTJOIN(",",1,IF(B2:B8=A2,A2:A8,"")),"") shows the problem that matches your explanation, so this must have been what you actually attempted. — Jim K
– Jim K, Commented Jul 9 at 15:46
Thanks @JimK, that improves the question (even if I initially feared that adding the A2:A8&","& would pollute, it makes clear we've got a strange Animal Banana). Additionally I fixed the A7s to A8s (I forgot to adapt the formulas when I added the Thing line later, to show the "without children" case). — Guillaume Outters
– Guillaume Outters, Commented Jul 9 at 15:58
Alright, that looks a lot better to me. Sorry to say, though, that I'm ending up where you did — an array formula for each row looks like the solution. Which doesn't seem great. — Jim K
– Jim K, Commented Jul 9 at 16:05
Would you consider a solution with LibreOffice Base? It should be simple with a database, and it's how I've solved these types of problems in the past. Or, what about MS Excel or Google Sheets — I've heard that both have functions that can filter text over rows. This might be the biggest thing that Calc is lacking. — Jim K
– Jim K, Commented Jul 9 at 16:27
@JimK That's nice to suggest Base but I must decline: in fact I come from an SQL background and just found it maddening that something I could so easily model in SQL did not apply on the first try. As for Microsoft or Google, I'm trying to stay away from anything proprietary, so that would be no. Or only as a base of "what function should we implement next into Calc?" or of "It works as intended in Excel, so this could become a LibreOffice bug report". — Guillaume Outters
– Guillaume Outters, Commented Jul 9 at 16:43

Jim K · Accepted Answer · 2025-07-10 00:56:10Z

1

Calc version 24.8 has FILTER(), which we can use to grab one match at a time. (This is what I had in mind in my apparently out-of-date comment above).

Formula for E2 (enter normally, not as an array formula):

=IFERROR(INDEX($A$2:$A$8, SMALL(FILTER(ROW($B$2:$B$8)-ROW($B$2)+1, $B$2:$B$8=$A2), COLUMN(A1))), "")

Drag to fill right to G2.

Now in D2, join the (potentially 4) results.

=IF(B2="", TEXTJOIN(", ", 1, A2, E2:G2), "")

Finally, select D2 through G2 and fill down.

The result in column D:

Row 2   Fruit, Apple, Banana
Row 3
Row 4   Animal, Pangolin, Duck
Row 5
Row 6
Row 7
Row 8    Thing

edited Jul 10 at 0:56

answered Jul 10 at 0:40

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guillaume Outters Jul 10 at 1:09

Alas this is still using an helper column, and I think I prefer replicating my formula; nonetheless what an investigation!

Collectives™ on Stack Overflow

Cell relative to current row in an array formula

Bonus question

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Bonus question

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related