=(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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!