Using Index Collect formula when there are multiple matches in the source sheet. (#Invalid Value)

SebastienD
edited 05/05/22 in Formulas and Functions

Hello,

My goal with using INDEX(COLLECT)) was to obtain information based on 3 different criteria, but in my source sheet there are multiple matches for the three criteria specified.


What do I need to add to the Index, Collect formula to overcome this or do I need to use a different formula combination?


Below is the formula:

=INDEX(COLLECT({Reviewer}, {Product}, [Product Name]@row, {Review Type}, [Review Type]@row, {Round}, [Round #]@row), 1)


Thanks,

Best Answer

  • SebastienD
    Answer ✓

    The criteria 1 row was missing an underscore that was present in the source sheet column so that's what the error was from, seems to be working now.

Answers

  • SebastienD
    Answer ✓

    The criteria 1 row was missing an underscore that was present in the source sheet column so that's what the error was from, seems to be working now.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SebastienD

    I'm glad you got it working! Thanks for posting your solution.

    Cheers,

    Genevieve