"Invalid Value" result from Index-Collect formula

twarner

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

jessica.smith

@twarner

I think the issue is that your cross-sheet references are formulas. Try wrapping the collect criteria in a Contains function.

=INDEX(COLLECT({YTD-ActualsByMonth_Video-PIDs: Contractor Cash}, {YTD-ActualsByMonth_Video-PIDs: PID}, Contains([PID text]@row,@cell), {YTD-ActualsByMonth_Video-PIDs: YRMO}, Contains("202401", @cell)), 1)

twarner

@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