Index/Collect that should work, but is not returning values.
Hello, Community! I've tried support and Pro Desk so far without success and could REALLY use your collective brain power. Here's the situation:
I'm trying to use Index/Collect to bring the value of A into D where the Year for Role = 0 and the Role matches the FS_Description (same value, just different label). The same scenario applies for C into B.
My formula (below) doesn't return the value but also doesn't error out.
=IFERROR(INDEX(COLLECT({A_0}, {Benefits Realization_Input Range 1}, [FS_Description]@row, {Benefits Realization_Input Range 2}, 0)), "")
I even tried a different version - but get the same result:
=IFERROR(INDEX(COLLECT({A_0}, {BRI_Role}, $[FS_Description]@row, {Y{Benefits Realization_Input Range 1}, 0), 1), "")
Am I missing something? Any thoughts?
Best Answer
-
Try this:
=IFERROR(INDEX(COLLECT({A_0}, {Benefits Realization_Input Range 1}, =[FS_Description]@row, {Benefits Realization_Input Range 2}, =0)), "")
or this:
=IFERROR(INDEX(COLLECT({A_0}, {Benefits Realization_Input Range 1}, =[FS_Description]@row, {Benefits Realization_Input Range 2}, =0), 1), "")
Answers
-
Try this:
=IFERROR(INDEX(COLLECT({A_0}, {Benefits Realization_Input Range 1}, =[FS_Description]@row, {Benefits Realization_Input Range 2}, =0)), "")
or this:
=IFERROR(INDEX(COLLECT({A_0}, {Benefits Realization_Input Range 1}, =[FS_Description]@row, {Benefits Realization_Input Range 2}, =0), 1), "")
-
Thanks, @Mike TV!
Help Article Resources
Categories
Check out the Formula Handbook template!