Index Match or Index Collect with multiple criteria

ShelbyShelby ✭✭✭✭✭
edited 11/19/20 in Formulas and Functions
11/19/20 Edited 11/19/20
Answered - Pending Review

I'd like to return a Contact to a cell if two criteria are met. I've tried using the index collect formula copied below but I continue to receive #INCORRECT ARGUMENT SET.

=INDEX(COLLECT({Vendor Contact}, {Project Name}, [Project Name]@row, {Scope of Work}, [Scope of Work]@row))


Solutions? 😁

Previous1

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    Hi @Shelby

    hope you are fine, could you please supply screen shot so i can help you.

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Hi @Shelby

    Try adding a 1 after the COLLECT function so the INDEX knows what to do:

    =INDEX(COLLECT({Vendor Contact}, {Project Name}, [Project Name]@row, {Scope of Work}, [Scope of Work]@row), 1)

    I'm checking this against Paul's explanation of an INDEX(COLLECT on this post, here.

    Let me know if that works for you!

    Cheers,

    Genevieve

  • ShelbyShelby ✭✭✭✭✭

    Hi @Genevieve P

    Thanks for the response. I added the 1 to the end of the formula and it came back with an #INVALID VALUE error. Here's the formula that's not working:

    =INDEX(COLLECT({Vendor Contact}, {Project Name}, [Project Name]@row, {Scope of Work}, [Scope of Work]@row), 1)

  • ShelbyShelby ✭✭✭✭✭

    @Bassam.M Khalil

    I've included screenshots of both sheets below:


  • Hi @Shelby

    Can you confirm that the Vendor Contact column in the source sheet and destination sheet are both Contact Columns? This error can sometimes happen if the formula is trying to output a specific value that doesn't match the column type (see here).

    You may also want to check the columns you're referencing to make sure there are no errors present in the source sheet; it could be that this formula is pulling through an error from one of these columns.

    If neither of this has described what's happening, it would be very helpful to see a screen capture of both sheets & the two referenced columns in the Cross-Sheet Reference pop-up window (but please block out any sensitive data!)

    Cheers,

    Genevieve

  • ShelbyShelby ✭✭✭✭✭

    @Genevieve P

    The Vendor Contact columns in the source and destination sheet are contact type columns. The source sheet only has two rows populated with no formulas present, I created it as a test. I've included two screenshots below that show both sheets with both cross sheet references highlighted:


  • ShelbyShelby ✭✭✭✭✭
    edited 11/19/20

    @Genevieve P

    It's working...but only in the row 1 😶

    I'm sure it has to do with the row index number


  • ShelbyShelby ✭✭✭✭✭

    @Paul Newcome

    Would greatly appreciate any insight you have to resolve the discussion in this thread...

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You are evaluating the [Scope of Work] column in row 2 of your last screenshot looking for "Doors", but I don't see that on your source sheet screenshot.

  • ShelbyShelby ✭✭✭✭✭

    @Paul Newcome

    I've worked out issues with my original formula and it's now working as needed. Thanks for your time on this!

  • JamieLimJamieLim ✭✭✭✭✭

    @Paul Newcome would you mind sharing what issues you had with your original formula and you overcame it? I'm trying to do something similar. Appreciate if you could advice on this. Thank you!

  • ShelbyShelby ✭✭✭✭✭

    @JamieLim

    Can you share info on what you're trying to do?

  • JamieLimJamieLim ✭✭✭✭✭

    @Shelby Thanks for reaching out. I guess there was an issue with the reference range. I worked through the reference range and got this resolved. Thanks once again!

  • @Shelby @JamieLim Would either of you be kind enough to provide what your solutions were? I'm looking to pull a Rate value if two other criteria are met, and the type of formulas that you were working on seem to be what I'm looking for. Thank you!

  • ShelbyShelby ✭✭✭✭✭
    edited 04/20/21

    @AnnieSE I've pasted a sample formula below for your review as well as screenshots. Try modifying the formula below with the correct references to use in your sheet. Let me know if this helps or if you need more info.


    =INDEX(COLLECT({Rate Value}, {Criterion 1 Range}, [Criteria 1]@row, {Criterion 2 Range}, [Criteria 2]@row), 1)


Sign In or Register to comment.