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
Check out the Formula Handbook template!