Index Match or Index Collect with multiple criteria

Shelby
Shelby ✭✭✭✭✭✭
edited 11/19/20 in Formulas and Functions

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? 😁

«1

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Shelby

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

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Shelby
    Shelby ✭✭✭✭✭✭

    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)

  • Shelby
    Shelby ✭✭✭✭✭✭

    @Bassam.M Khalil

    I've included screenshots of both sheets below:


  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Shelby
    Shelby ✭✭✭✭✭✭

    @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:


  • Shelby
    Shelby ✭✭✭✭✭✭
    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


  • Shelby
    Shelby ✭✭✭✭✭✭

    @Paul Newcome

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

  • Paul Newcome
    Paul 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.

    thinkspi.com

  • Shelby
    Shelby ✭✭✭✭✭✭

    @Paul Newcome

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

  • JamieLim
    JamieLim ✭✭✭✭✭✭

    @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!

  • Shelby
    Shelby ✭✭✭✭✭✭

    @JamieLim

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

  • JamieLim
    JamieLim ✭✭✭✭✭✭

    @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!

  • Shelby
    Shelby ✭✭✭✭✭✭
    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)