Forum Discussion
Struggling with LAMBDA in my LET statement
- Jul 20, 2024
=LET(
ATTRIBUTE_NAME,
VSTACK("P_SHAPE_CD","P_WEIGHT_G_AMT","P_WEIGHT_KG_AMT","P_WEIGHT_LB_AMT","THEME_MULTY_CD","TYPE_OF__CD","WHY_CD","_HEIGHT_CM_QTY","_HEIGHT_FT_QTY","_HEIGHT_IN_QTY","_LENGTH_CM_QTY","_HEIGHT_FT_QTY","_LENGTH_IN_QTY","_WEIGHT_KG_AMT","_WEIGHT_LB_AMT","_WIDTH_CM_QTY","_WIDTH_IN_QTY","_WIDTH_MM_QTY"),
TRANSPOSE(
HSTACK(
VSTACK("P_NUM",ATTRIBUTE_NAME),
DROP(
REDUCE("",SORT(UNIQUE(A2:A147)),LAMBDA(u,v,HSTACK(u,VSTACK(v,BYROW(ATTRIBUTE_NAME,LAMBDA(r,XLOOKUP(v&r,A2:A147&F2:F147,G2:G147,"")))))))
,,1))))
This formula returns the expected result in my sample file.
NotSoFastEddie It looks like your formula is attempting to use MAP within BYROW to output an array of arrays, which is currently not supported in Excel.
One possible workaround is to generate one or more arrays of lookup_values with the same dimensions as the output array and use MAP to loop through them together.
Example 1: MAP with multiple lookup_value arrays
=LET(
p_num, A2:A147,
attrib_name, F2:F147,
attrib_value, G2:G147,
ARR, LAMBDA(n, CHOOSE(n, UNIQUE(p_num), TOROW(UNIQUE(attrib_name)))),
results, MAP(ARR({1}), ARR({2}), LAMBDA(num,name, XLOOKUP(1, (p_num = num) * (attrib_name = name), attrib_value, ""))),
HSTACK(VSTACK("P_NUM", ARR(1)), VSTACK(ARR(2), results))
)
Example 2: MAP with a single, concatenated lookup_value array
=LET(
p_num, A2:A147,
attrib_name, F2:F147,
attrib_value, G2:G147,
lookup_array, p_num & "|" & attrib_name,
nums, UNIQUE(p_num),
names, TOROW(UNIQUE(attrib_name)),
results, MAP(nums & "|" & names, LAMBDA(v, XLOOKUP(v, lookup_array, attrib_value, ""))),
HSTACK(VSTACK("P_NUM", nums), VSTACK(names, results))
)
However, in this particular example, MAP is not even needed:
=LET(
p_num, A2:A147,
attrib_name, F2:F147,
attrib_value, G2:G147,
lookup_array, p_num & "|" & attrib_name,
nums, UNIQUE(p_num),
names, TOROW(UNIQUE(attrib_name)),
results, XLOOKUP(nums & "|" & names, lookup_array, attrib_value, ""),
HSTACK(VSTACK("P_NUM", nums), VSTACK(names, results))
)
See attached...
- NotSoFastEddieJul 21, 2024Brass ContributorI have to ask DJ, what editor are you using? I don't see this type of syntax highlighting in my EXCEL.
- djclementsJul 21, 2024Bronze Contributor
NotSoFastEddie The coloring has nothing to do with Excel. When posting on this website, you can use the </> button found on the toolbar to "Insert/Edit code sample", then select Excel Formula from the "Language" dropdown. To view the toolbar when posting a Reply, click the "Open full text editor" link found just below the initial "Enter reply text" frame.
- NotSoFastEddieJul 21, 2024Brass ContributorThanks again DJ! It looks very good.