Hi folks!

I'm having difficulty with an Index-Collect 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({YTD-ActualsByMonth_Video-PIDs: Contractor Cash}, {YTD-ActualsByMonth_Video-PIDs: PID}, [PID text]@row, {YTD-ActualsByMonth_Video-PIDs: YRMO}, "202401"), 1)

Here is the formula sheet:

Here is the source/reference sheet:

Things I've tried:

1. 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.
2. Even though I need to use INDEX-COLLECT function, as I have 2 criteria to check for, I tried this with an INDEX-MATCH as well to make sure my references all checked out:

=INDEX({YTD-ActualsByMonth_Video-PIDs: Other Contractor}, MATCH([PID text]@row, {YTD-ActualsByMonth_Video-PIDs: 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({YTD-ActualsByMonth_Video-PIDs: Contractor Cash}, {YTD-ActualsByMonth_Video-PIDs: 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