Forum Discussion
Struggling with LAMBDA in my LET statement
=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...
