Using BYROW and LAMBDA for TEXTJOIN in Google Sheets
In Google Sheets, we often use ARRAYFORMULA to apply a formula to an entire range of cells. However, ARRAYFORMULA does not work with TEXTJOIN.
To address this, Google has introduced new functions: BYROW and LAMBDA. These functions can be combined to achieve the same result as using TEXTJOIN with ARRAYFORMULA.
New Functions:
BYROW: Google Support Reference
LAMBDA: Google Support Reference
Example:
You can use a combination of BYROW and LAMBDA to create an array formula for TEXTJOIN. Here’s an example formula:
=BYROW(A1:A, LAMBDA(row, TEXTJOIN(",", TRUE, FILTER(QueryVlookUp!D1:D, QueryVlookUp!C1:C = row))))
This formula works as follows:
BYROW(A1:A, LAMBDA(row, ...)): Applies the LAMBDA function to each row in the range A1:A.
LAMBDA(row, TEXTJOIN(",", TRUE, FILTER(QueryVlookUp!D1:D, QueryVlookUp!C1:C = row))): For each row, it uses TEXTJOIN to concatenate values from QueryVlookUp!D1:D where the corresponding value in QueryVlookUp!C1:C matches the current row.
Example Google Sheet:
You can make a copy of a Google Sheet with this setup to see it in action.
Previous post: https://www.pyisoe.com/2024/09/google-sheets-formulas-like-relational.html
No comments:
Post a Comment