Forum Discussion
Return value depending on numbers in multiple other columns
I've converted 'data' and 'items' to dynamic named ranges and updated 'GetData' so it will still calculate if an item is missing.
For editing named items and functions, I'd recommend using Advanced Formula Environment (Part of Excel Labs):
This will do it. It's not too bad after the data arrangement is changed so REDUCE can FILTER recursively.
'GetData Lambda
=LAMBDA(input,LET(
description, DROP(REDUCE("", items, LAMBDA(a, v, VSTACK(a, EXPAND(v, 3, , v)))), 1),
NMatrix, WRAPROWS(TOCOL(data), 2),
DROP(
REDUCE(
"",
SEQUENCE(20),
LAMBDA(a, v,
LET(
results, FILTER(
description,
(TAKE(NMatrix, , 1) = input) * (TAKE(NMatrix, , -1) = v),
""
),
VSTACK(a, TEXTJOIN(" / ", , results))
)
)
),
1
)
))All you have to do at the sheet level is supply the 'input' (Numbers 1 to 6):
Patrick2788that works so well.
Thank you!I've found that I can edit your code in Name Manager. Is this the right place to work on it?
What value should I edit if I want to add columns to the "data"?
If there is an empty cell in the "items" column, then all results in "list1" will return a wrong data type error.
Is there a way to solve this?
If there is not, I can populate that column with temporary data that can be overwritten when the file is used.I've converted 'data' and 'items' to dynamic named ranges and updated 'GetData' so it will still calculate if an item is missing.
For editing named items and functions, I'd recommend using Advanced Formula Environment (Part of Excel Labs):
- You're awesome!
