=(INDEX(COLLECT Invalid Value Error
I am getting an Invalid value error in this formula:
=(INDEX(COLLECT({Zit Zap 102621 Units Needed}, {Zit Zap 102621 Formula}, Formula@row, {Zit Zap 102621 Inv ID}, [RM Inventory ID]@row), 1))
I'm trying to bring in the RM Units Needed from Spreadsheet 2 when the Formula and RM Inventory ID from Spreadsheet 2 matches the Formula and RM Inventory ID on Spreadsheet 1. The error is on Spreadsheet 1.
Any suggestions?
Best Answer

The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).
Answers

I'm adding another screenshot to show what's at the top of Spreadsheet 2 in case that's causing a problem.

The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).

You have a keen eye! Thank you!

Happy to help. 👍️
I just did that to myself earlier today and spent quite a bit of time and effort unsuccessfully troubleshooting, so it was still a fresh thought to look for that. Hahaha

@Paul Newcome Thank you, I just had the same INVALID VALUE result from an INDEX(COLLECT) function, then realized that one of the conditions in the COLLECT was pulling in zero results. This is not easy to track! I solved it with the IFERROR function, which is not an elegant solution but it works.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!