Index/collect returning #no match when there is a match

Jessica Abbey

I keep getting a #NO MATCH when clearly I can see the match. I have used index/collect nested in several IF functions with success, but I cannot see why I'm getting NO MATCH here/

=INDEX(COLLECT(ActualNAR:ActualNAR, Mth:Mth, 3, SCML:SCML, SCML@row), 0)

ericncarr

Try putting a 1 at the end of your formula before the last parenthesis. In an Index(Match()) formula you can use 0 but in an index(collect()) formula I've never used 0...1 gives the first result in the Collect range.

Jessica Abbey

Changing it to 1 still ends up in a NO MATCH result.

Andrée Starå

Hi @Jessica Abbey

I hope you're well and safe!

This should work.

=INDEX(COLLECT(ActualNAR:ActualNAR, Mth:Mth, 3, SCML:SCML, SCML@row), 1)

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jessica Abbey

Now I'm getting Invalid Operation.

Andrée Starå

@Jessica Abbey

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)