0

I have a list of items and in another place I have list that covers some of these items each time with a different key.

How to fill in column B all matching results next to the original item (column A) from column E without dragging the formula down manually?

A B C D E
1 Item Matching keys Item Key
2 foo apple,testing foo apple
3 anything bar orange
4 bar orange,something foo testing
5 another thing bar something

If dragging down then B2 and below will be:

=join(",", iferror(filter(E:E, D:D=A2), ""))
=join(",", iferror(filter(E:E, D:D=A3), ""))
=join(",", iferror(filter(E:E, D:D=A4), ""))
=join(",", iferror(filter(E:E, D:D=A5), ""))

Ideally B2 would have been:

=arrayformula(join(",", iferror(filter(E:E, D:D=A2:A), ""))

But both array join and filtering by a more than 1 value (D:D=A2:A) aren't supported by Google Sheets.

Queries also don't seem to support it: =arrayformula(join(",", query(D:F, "select Col2 where Col1='" & A2:A & "'"))) acts as if it's just & A2 &.

It's a bit similar to How to concatenate all further columns from duplicated items of a first column? (which utilized a LET function), except this time it should be applied inside the original list and not as as standalone summary.

2 Answers 2

0

Use map(), tocol() and filter(), like this:

=map(tocol(A2:A, 1), lambda(item, 
  ifna(join(", ", filter(E2:E, item = D2:D))) 
))

See map(), tocol() and filter().

6
  • Interesting. Is it better/faster than the arrayformula/byrow version? Also, why writing it as filter(E2:E, item = D2:D) instead of filter(E2:E, D2:D = item) when either works and the latter might look more understandable?
    – LWC
    Commented May 29 at 15:54
  • You're right about arrayformula(), I've removed it. But those optimization tips don't claim that iferror is an anti-pattern. Does your usage of ifna not expand all the way the bottom too?
    – LWC
    Commented May 30 at 17:05
  • Alright, I've removed the value. Now it's down to byrow VS the combination of map and tocol.
    – LWC
    Commented May 30 at 18:04
  • The results are identical indeed, I just wonder which is quicker with large datasets.
    – LWC
    Commented May 30 at 19:19
  • Please ask only one question per post. Post a new question instead. Commented May 30 at 19:38
0

Finally I've managed to do it via Filter assisted by BYROW/LAMBDA, hope it'll help others to put this in B2:

=IFERROR(BYROW(filter(A2:A, A2:A<>""), LAMBDA(a, JOIN(",", FILTER(E:E, F:F = a)))))

The IFERROR is needed to avoid writing #N/A in cells that have no matches.

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.