Index Match or Index Collect with multiple criteria

Shelby

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

Bassam Khalil

Hi @Shelby

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

Genevieve P.

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

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

@Bassam.M Khalil

I've included screenshots of both sheets below:

Genevieve P.

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

@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

@Genevieve P

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

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

Shelby

@Paul Newcome

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

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.

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

@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

@JamieLim

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

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!

AnnieSE

@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

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