If/Index/Collect Across two sheets
I'm getting an invalid value error but I'm not sure why with the following formula:
=IF([Journal Source]@row = "Supplier Invoice", INDEX(COLLECT({Forecast Sheet Row Number}, {Forecast Sheet Supplier}, Supplier@row, {Forecast Sheet Month}, [2023 Month]@row), 1))
Referenced Sheet:
Original Sheet (With Formula in the matching forecast column):
Here's my attempted logic with the formula:
If Journal source is Supplier invoice, then return the row number on the condition that the supplier and month match.
Ideally I want the formula to lookin within the other sheet to find that match. But the invalid value is throwing me off
Q's:
 Why do we have an invalid value and how can I fix it?
 Am I using the right formula for my logic?
Answers

Is [2023 Month]@row from the formula on the "original sheet" or on the Forecast sheet?
From the Formula error help page (linked in my signature):
#INVALID VALUE error:
Cause:
The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character "H" in the text string "Hello" has a starting position of 100 characters...
=FIND("H", "Hello", 100)
...Since the string "Hello" is 5 characters long, it isn't possible to have FIND start looking for the character "H" at 100 characters in. Only the numbers 15 can be accepted.
Resolution:
Make sure that all number arguments are within the range that the function expects.
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 213 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
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!