Question about Lambda

[FT00]

Hi, i am very confuse about the syntax of Lambda, please refer attached.
I am doing a Textjoin by criteria thing. (its own exercies)
My formula in D2 work, but i Memories it rather than Understand it.
I do not understand why is it like that. I written down my question in the excel in yellow.

Also, after i textjoin, i try to textsplit back to original using formula, but i do not know how.


Anyway can help shine some light?

[CheeseSandwich]

From my understanding, within a LET formula, the letters x and q are like variables, then only become what you define them as. for example:

Please Login or Register  to view this content.

Will mean that the value of x becomes the below:
A
B
C
D

So x is an array of values and not just one.

With that in mind, when you use the BYROW function, it requires a LAMBDA in order to loop through the values of x. For example:

Please Login or Register  to view this content.

In the above, the q becomes A then B then C then D as the LAMBDA loops through the values of x.

So the LAMBDA part is doing a TEXTJOIN using each value within x by looking up the values in your table, so it finds:

A - China, SG
B - SG, Msia
Etc..

The formula then stacks horizontally, the result of x and then the results for the TEXTJOIN's.

Sorry if this is not clear, this is the way I learned how the LAMBDA works, I actually come from a VBA coding background.

As for the formula to convert the values back, the below could be an option, although there may be many variations that do the same:

Please Login or Register  to view this content.

[HansDouwe]

Here is another solution to convert the values back:

Please Login or Register  to view this content.

[FT00]

@CheeseSandwich

Hi, thanks for the detailed explanation. Does that mean the q as in "Lambda(q" can only refer to a single cell?

[FT00]

Hi Hans, thanks for your solution. Helpful as i can better understand how the Lambda works.

[CheeseSandwich]

q refers to a single instance of x

[HansDouwe]

HDoes that mean the q as in "Lambda(q" can only refer to a single cell?

No.
In BYROW(x,LAMBDA(q,... q ferfers each time to a single row: The first time q refers to the first row of x, the second time q refers to the second row of x etcetera,
where in your example x is equal to UNIQUE(Table1[Name])

In MAP(x,LAMBDA(q,..., q can only refer ro a single cell.

[FT00]

Thanks Hans for your explanation. Sorry for the late reply.

Sorry, i am still trying to learn Lambda logic, hence might ask quite stupid question....=(

[AliGW]

No such thing - until you know, you don't know. I progress in fits and starts with LAMBDA, with a lightbulb moment every now and again. Lots of tiral and (frustrating) error will get you there eventually.