"Invalid Value" result from IndexCollect formula
Hi folks!
I'm having difficulty with an IndexCollect formula resulting in "Invalid Value" error. I've simplified the formula to the absolute basics to try to isolate the problem.
Here is my current formula:
=INDEX(COLLECT({YTDActualsByMonth_VideoPIDs: Contractor Cash}, {YTDActualsByMonth_VideoPIDs: PID}, [PID text]@row, {YTDActualsByMonth_VideoPIDs: YRMO}, "202401"), 1)
Here is the formula sheet:
Here is the source/reference sheet:
Things I've tried:
 Converted all values to text to remove issues there. So PID and YRMO are confirmed text values in the resource sheet and PID is confirmed text in the formula sheet.
 Even though I need to use INDEXCOLLECT function, as I have 2 criteria to check for, I tried this with an INDEXMATCH as well to make sure my references all checked out:
=INDEX({YTDActualsByMonth_VideoPIDs: Other Contractor}, MATCH([PID text]@row, {YTDActualsByMonth_VideoPIDs: PID 2}, 1)))
This works  it brings back the first value it finds that matches the PID criteria: see above "Formula sheet" screenshot
To compare apples to apples, I reduced my INDEX/COLLECT formula down to just 1 criteria:
=INDEX(COLLECT({YTDActualsByMonth_VideoPIDs: Contractor Cash}, {YTDActualsByMonth_VideoPIDs: PID}, [PID text]@row), 1)
Same results: see above "Formula sheet" screenshot
What am I doing wrong with my INDEX/COLLECT formula that is resulting in the "INVALID VALUE" error using all the same data and references as my INDEX/MATCH formula?
Thanks for the help!
Tony
Answers

I think the issue is that your crosssheet references are formulas. Try wrapping the collect criteria in a Contains function.
=INDEX(COLLECT({YTDActualsByMonth_VideoPIDs: Contractor Cash}, {YTDActualsByMonth_VideoPIDs: PID}, Contains([PID text]@row,@cell), {YTDActualsByMonth_VideoPIDs: YRMO}, Contains("202401", @cell)), 1)

@jessica.smith  thank you for your answer. I've never had that issue with other Collect formulas I've written. However, I did try your suggestion, AND IT WORKED! So, I will not question it further. ;)
Thanks for your help!
Regards,
Tony
Help Article Resources
Categories
Check out the Formula Handbook template!