What is wrong with this formula?

CAKutsh

I keep getting invalid value. Can someone assist?

=INDEX(COLLECT({Last Name First1}, {Sector}, [Last Name, First Name]@row), 1)

CAKutsh

Apparently, I am what was wrong with the formula. It worked, thank you!

L_123

Syntax looks correct. Double check your references, sometimes smartsheet can delete/modify references when they are first made.

2 Questions

1. Do you want to return the value in "Last Name First1" or "Sector"?
2. Are all of your references the same number of cells?

CAKutsh

Thank you for the quick response. I want to return the value of sector and yes, references are the same number of cells

Jeff Reisman

If you're trying to collect the value from Sector, that range should be listed first, followed by your criteria range, and then criteria. The syntax for INDEX/COLLECT is INDEX(COLLECT(range to collect, criteria range, criteria), 1)

=INDEX(COLLECT({Sector}, {Last Name First1}, [Last Name, First Name]@row), 1)

CAKutsh

I will try it. Many Thanks!

CAKutsh

Apparently, I am what was wrong with the formula. It worked, thank you!