Index/Collect that should work, but is not returning values.

CNB_Chris_Hallo

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?

Mike TV

@CNB_Chris_Hallo

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), "")

Mike TV

@CNB_Chris_Hallo

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), "")

CNB_Chris_Hallo

Thanks, @Mike TV!