#INCORRECT ARGUMENT SET on Index/Collect Formula

Matthew L

I'm building a dynamic dashboard with multiple filters. I'm making a Index/Collect formula that can read values on a source sheet based on the values selected via the filters. This formula should work and I've seen it work on other sheets that Pro-desk helped with, and they gave me, but for some reason, I'm getting a #INCORRECT ARGUMENT SET error.

=INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, {Selected Season}, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

Does anyone have any thoughts/ideas on what might be causing this error with this INDEX/COLLECT formula? I feel like it may be a syntax error but I'm not quite sure what it would be.

Carson Penticuff

Without seeing your sheet setup, it is hard to say. Is it possible that "Selected Season" was meant to be a reference to the current sheet instead of the source sheet?

KPH

Hi @Matthew L

I think the issue is with Season.

Without seeing the sheets I am not sure, but it looks like ...

Your Budget Sheet A has columns:

• USD
• Season
• Campaign

And the sheet you want to put this formula in (I'll call sheet B) has columns:

• Primary Column
• Selected Season

And you want to collect the USD where the Season in Budget Sheet A matches the Season in the Sheet B and the Campaign in Sheet A matches the Campaign in Primary Column row 2.

The formula you are using has a cross sheet reference for Selected Season (see bold). If this is a range of cells this won't work.

=INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, {Selected Season}, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

If you change this to a cell reference in this sheet it will work. Here I have put it as row 2 in the Selected Season column. You can make the row number whatever you want or replace $2 with @row to use the current row.

=INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, [Selected Season]$2, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

Does that help?

Matthew L

@Carson @KPH The {Select Season} value is from a metrics sheet, so it's a cross sheet reference. It's not a range either, it's a single cell. Could this be what's causing the error still?

Edit: So the way I intended this formula to work is that it would search the season column on Budget Sheet A and match rows that just have the Selected Season. Then I want the value to also match the Campaign name in Budget Sheet A to the Campaign name listed in [Primary Column]$2

KPH

Hi @Matthew L

Can you try putting the Select Season into the other sheet and see if the formula works? That will confirm where the issue is.

Matthew L

@KPH Just tried putting the Select Season into the other sheet and I'm still getting the same #INCORRECT ARGUMENT SET error.

KPH

Can you share the formula you are now using?

Matthew L

@KPH The formula I just tried was this one:

=INDEX(COLLECT({Australia Budget-USD}, {Australia Budget-Season}, [Test Budget]38, {Australia Budget-Campaign}, [Primary Column]$2), 1)

Considering this change wasn't the solution I'd like to keep my season value on my metrics sheet. The same cell reference has worked on other Index/Match formulas that I've written so I really don't believe that is the issue. Since this is a #INCORRECT ARGUMENT SET error I believe the issue is with the syntax of this Index/Collect formula.

This is the formula that the Pro-desk provided me so I believe it's structured the right way but there's just something wrong with the syntax at the end of the collect formula I think:

=INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, {Selected Season}, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

KPH

Hi

I am trying to solve the issue by process of elimination: Testing each part to see where it is failing for you. By moving the Selected Season we have ruled that out.

The syntax looks fine. I have the formula working. It has to be something to do with the references.

Maybe we try this the other way around and I share my copy to see if you can spot something?

This is the formula I am using:

=INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, {Selected Season}, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

This is my first sheet

The ranges that start {Budget Sheet...} are references to the full columns with the same name

The range {Selected Season} is a single cell, row 1 in Extra Column

This is my second sheet

The formula above is placed in both cells of Column2 and gives the expected result.

Hope that helps.

Matthew L

@KPH Thanks for your thoughtful answer here. I've triple-checked all my references(they are all correct) and it's still giving me the same #INCORRECT ARGUMENT SET error.

I understand how it is supposed to work but for some reason, this same formula isn't working in my sheet. I've submitted a support ticket for this as well but they haven't been responding. Based on your response I wonder if this is perhaps a bug rather than an issue with the formula itself since it's been working for you and it worked when I tried using the same formula on a different sheet with the Pro Desk.

If anyone else has any insight into why I'm receiving this error let me know!

Carson Penticuff

A few troubleshooting steps that you could potentially try

• Delete all of the relevant references and recreate them. I see that you have already verified them, but this is taking it one step further just to be sure.
• I'm not sure how complex your sheets are or what other references or formatting may be included, but if the effort involved is not overly substantial, you could try recreating the sheets and copy/pasting the information into the new versions.
• Modify the affected sheets and save using a different PC and/or browser.
• Use alternative formulas to evaluate individual functions within the formula.
  • =COUNTIFS({Budget Sheet A-Season}, {Selected Season})
  • =COUNTIFS({Budget Sheet A-Campaign}, [Primary Column]$2)
  • =COUNTIFS({Budget Sheet A-USD}, @cell <> "")
  • =INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Season}, {Selected Season}), 1)
  • =INDEX(COLLECT({Budget Sheet A-USD}, {Budget Sheet A-Campaign}, [Primary Column]$2), 1)

This may or may not provide you with any useful information. When I find myself in a situation where something I know should work is not working, I try to break down the individual components into the smallest subsets possible and evaluate them from every possible angle. I often find the issue is something I have already verified to be correct, but that I did not verify all possible errors.

Matthew L

@Carson Penticuff @KPH Thanks again for both of your help troubleshooting this formula. I'm still not sure what caused it, but when I signed on yesterday morning after taking a few days off for Thanksgiving the formula was miraculously working again!

Nobody else had accessed the sheet since I was last in it on 11/22 so it must've just been a Holiday miracle that fixed the bug. Since it resolved on it's own somehow I believe this was actually a bug and not an issue with the formula which I had initially suspected due to the error message that I was receiving.

Carson Penticuff

@Matthew L I'm glad it's working for you. Gremlins.... they happen.

KPH

Thanks for letting us know!