If/Index/Collect Across two sheets

clopez365

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?

Jeff Reisman

@clopez365

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 1-5 can be accepted.

Resolution:

Make sure that all number arguments are within the range that the function expects.