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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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
Check out the Formula Handbook template!