What is wrong with this formula?
I keep getting invalid value. Can someone assist?
=INDEX(COLLECT({Last Name First1}, {Sector}, [Last Name, First Name]@row), 1)
Best Answer

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

Syntax looks correct. Double check your references, sometimes smartsheet can delete/modify references when they are first made.
2 Questions
 Do you want to return the value in "Last Name First1" or "Sector"?
 Are all of your references the same number of cells?

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

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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

I will try it. Many Thanks!

Apparently, I am what was wrong with the formula. It worked, thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!