Google Sheets offers passing in parameters to lambdas as such: =LAMBDA(x, y, x y)(100, 200)

I was thinking of taking 2 columns from another Sheet, filter it with QUERY and then pass those 2 columns into the LAMBDA. Basically the 2 columns were a key and a CSV text that I wanted to split in one go.

1. =lambda(a, b, split(b, ","))(query('Alias Key Raw'!A1:B, "select * where A starts with 'X'"))

This gives the following ERROR Wrong number of arguments to call following LAMBDA function. Expected 2 arguments, but got 1 arguments.. Given that QUERY provides 2 columns of actual values, I thought this would be possible.

2. =byrow(query('Alias Key Raw'!A1:B, "select * where A starts with 'X'"), lambda(row, split(row, ",")))

This gives me only column A. No error otherwise. All of column B is ignored it appears

3. I've tried using BYCOL, BYROW, etc, and a lot of errors are ERROR Wrong number of arguments to call following LAMBDA function. Expected 2 arguments, but got 1 arguments.

Data

Input into the lambda

Expected

Output from the lambda

Note: There can be any number of lineages in the CSV cell

CodePudding user response：

Updated

=ArrayFormula(
 LAMBDA(a, {QUERY({a},"Select Col1"),SPLIT(QUERY({a},"Select Col2"),",")})
 (QUERY('Alias Key Raw'!A1:B, "select * where A starts with 'X'",1)))

Explanaition:

using an Array {} to return:
Col1: {QUERY({a},"Select Col1"),...}
Col2: {...,SPLIT(QUERY({a},"Select Col2"),",")}

Of the Query QUERY('Alias Key Raw'!A1:B, "select * where A starts with 'X'",1) found in the Lambda call named a

Used formulas help
ARRAYFORMULA - LAMBDA - QUERY - SPLIT

CodePudding user response：

Perhaps worth pointing out that the expected data can be returned with a more compact formula that uses neither QUERY nor LAMBDA:

=filter({A1:A,split(B1:B,",")},regexmatch(A1:A,"^X"))