#Incorrect Argument Set

ljkeefe1

Hello All,

I thought this would be a simple task, but about three hours in of trying to figure this out, I'm at a loss.

I have a total of 5 sheets, 1 reference sheet and 4 intake sheets created from the reference sheet. I'd like to take the information from the 4 sheets and "link" them to the reference sheet by title column that is used in all 5 sheets (the titles are exactly the same).

The column title that has the info I need is titled RCW/Code/Regulatory Authority on all 5 sheets.

I'm using IFERROR because the 4 sheets originated from the 1 reference sheet. I wanted teams to fill out information only pertaining to their team in the RCW/Code/Regulatory Authority column, so I made individual sheets for each from the 1 reference sheet, so basically the same sheet 4 times. That's why the titles in the primary column are the same.

I have tried several variations of the formula below and get an Incorrect Argument Set error each time. The formula below is the most recent one I tried.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {Data Responses - EngineeringRCW1}, 0)), {DR Eng2}, INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {DR EnviroProg2}, 0)), {Data Responses - Enviro Prog1}), INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {DR-Ops2}, 0)), {DR-Ops1}), INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {DR SDA2}, 0), {DR SDA1}), INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {DR TOSE2}, 0), {DR TOSE1})))

Please help 😊

Thank you,

Linda

KPH

It looks like an INDEX MATCH will do what you need. I will talk you through the steps to create the first formula so you can see what it does and check that meets your needs. Then you can edit it to work on the remaining columns.

Here is my mock up of your Index sheet

And here is my version of your reference sheet

I start with an INDEX formula and use the option in the pop up to Reference Another Sheet.

The column I reference is the column I want to be pulled into the new sheet. I select that column and give it a name.

The name is then added to my formula .

Then I continue with the formula and use a MATCH function to identify the row to pull across.

The first part of the MATCH is the thing to match on, the thing to look for. Which is the value in the Data source column.

Next, I use the Reference Another Sheet option again to set up a cross sheet reference to the column in the Index sheet that contains the Data source.

Then I add a 0 to find an exact match

The sheet then pulls the data from the RCW column where the Data source on the row matches the Data source on the reference sheet.

If I add something that is not in the index sheet an error is returned.

So I wrap the formula in an IFERROR, to return "" if there is an error.

Is this what you want to do?

Here is the formula so far if you want to copy/paste (note - you will need to set up the cross sheet references locally)

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), "")

Now, if you want to check another Index sheet if there is no match in the first one you can replace the "" we added for No Match with a repeat of the formula, but with cross sheet references to your second index.

Changing the first reference to the second sheet

And the second reference

The formula is now

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)), ""))

Now, if the reference is not found in the first sheet, it will look in the second one.

You can extend this for additional index sheets in the same way to look in a third sheet (addition is in bold):

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)),

IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 3 RCW}, MATCH([Data source]@row, {INDEX sheet 3 Data source}, 0)), "")))

You can use the same process to create a formula for the other two columns, just change the first sheet reference (the one in the index) in each of your INDEXES.

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), "")

Does that achieve what you want?

KPH

The Incorrect Argument Set error means there is probably a comma missing (or one extra) within the formula. The fact it only occurs on some rows means it is only a problem on the parts of the formula where the preceding IFs have been false.

For example, this formula will fail only if there is no match found against Sheet 1 and Sheet 2 as the error (the comma in bold on the last row) is after the point where the formula would stop being evaluated if a match had been found.

=IFERROR(INDEX({INDEX sheet 1 RCW}, MATCH([Data source]@row, {INDEX sheet 1 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 3 RCW}, MATCH([Data source]@row,, {INDEX sheet 3 Data source}, 0)), "")))

So, the first step in troubleshooting would be to look at the last part and work backwards. You can split the formula up and test each in turn, if that is easier.

KPH

It's a pickle Linda, but you can do this!

Lets start with your first INDEX COLLECT and see if we can get that working without anything else.

=INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {Data Responses - EngineeringRCW1}, 0))

This part that I highlighted is the range to collect from, but you have just one cell.

This part is the column to look in for your logic and seems OK

The 0 means it will collect from the range where the cell in the EngineeringRCW1 column has a value of 0.

Can you change the cell reference here to be a column reference that contains the values you want to return if the EngineerngRCW1 is 0

=INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {Data Responses - EngineeringRCW1}, 0))

ljkeefe1

@KPH Thank you for your reply.

I'm not sure what you are asking me to do below.......

This part that I highlighted is the range to collect from, but you have just one cell.

This part is the column to look in for your logic and seems OK

The 0 means it will collect from the range where the cell in the EngineeringRCW1 column has a value of 0.

Can you change the cell reference here to be a column reference that contains the values you want to return if the EngineerngRCW1 is 0

I did figure that I may have had the formulas in Collect function pointing to the wrong sheet and column.

So my reference sheet has a column for the Data source title and the RCW/Code/Regulatory Authority column, which is empty. However, the RCW/Code/Regulatory Authority column was filled out in the other 4 sheets and i want to bring that data into the reference sheet. That means that for source titles will be blank on 3 of the 4 intake sheets, that's why I worked in the IFERROR.

I modified the formula to account for my mistake earlier, because if I understand COLLECT correctly, I need to point it to my reference sheet column with the Data source title, correct?

=IFERROR(IFERROR(IFERROR(INDEX({DR Eng2}, COLLECT([Data source]@row, {Data Responses - EngineeringRCW1}, 0)), INDEX({Data Responses - Enviro Prog1}, COLLECT([Data source]@row, {DR EnviroProg2}, 0)), INDEX({DR-Ops1}, COLLECT([Data source]@row, {DR-Ops2}, 0)), INDEX({DR SDA1}, COLLECT([Data source]@row, {DR SDA2}, 0)), INDEX({DR TOSE rcw}, COLLECT([Data source]@row, {DR TOSE2}, 0)))))

Logic is: INDEX({RCW/Code/Regulatory Authority column from team sheet}, COLLECT([Data source title from reference sheet]@row, {data source title from team sheet},0)

KPH

I wanted to just get the first INDEX COLLECT working and then add in the others and the IFERRORs as it is easier to troubleshoot a small part at a time. The first reference after your COLLECT should be a range to collect data from (a column or a cross sheet reference to a column). I would like to try just using this part of the formula:

=INDEX(COLLECT([RCW/Code/Regulatory Authority]@row, {Data Responses - EngineeringRCW1}, 0))

But changing the part in bold to refer to the column that you want to collect the data from.

If that doesn't make sense, can you share screen shots of your reference sheet and one of your index sheets?

ljkeefe1

@KPH Got it. I changed it and I got an error: #unparseable. DR Eng2 is the data I want, Data Responses-EngineeringRCW1 is the lookup value

ljkeefe1

KPH

Hi

This part in bold is still a references to a single cell. The collect function is looking for a range of cells to collect data from.

=INDEX(COLLECT([DR Eng2]@row, {Data Responses - EngineeringRCW1}, 0))

Do you want to collect from the column called DR Eng2? If so, please try:

=INDEX(COLLECT([DR Eng2]:[DR Eng2], {Data Responses - EngineeringRCW1}, 0))

Note - There is also an extra { in your formula that should be removed:

Can you share screen shots of your reference sheet and one of your index sheets so I can get a better idea of what you want the formula to do?

ljkeefe1

@KPH I tried the above, including removing the extra curly bracket and I receive an #unparseable error.

Here's my reference sheet:

The columns in blue is where I want to bring in the info from the index sheets.

Here's an index sheet:

So I want to bring in the info in the blue columns from the bottom sheet into the top sheet by the Data Source name.

KPH

It looks like an INDEX MATCH will do what you need. I will talk you through the steps to create the first formula so you can see what it does and check that meets your needs. Then you can edit it to work on the remaining columns.

Here is my mock up of your Index sheet

And here is my version of your reference sheet

I start with an INDEX formula and use the option in the pop up to Reference Another Sheet.

The column I reference is the column I want to be pulled into the new sheet. I select that column and give it a name.

The name is then added to my formula .

Then I continue with the formula and use a MATCH function to identify the row to pull across.

The first part of the MATCH is the thing to match on, the thing to look for. Which is the value in the Data source column.

Next, I use the Reference Another Sheet option again to set up a cross sheet reference to the column in the Index sheet that contains the Data source.

Then I add a 0 to find an exact match

The sheet then pulls the data from the RCW column where the Data source on the row matches the Data source on the reference sheet.

If I add something that is not in the index sheet an error is returned.

So I wrap the formula in an IFERROR, to return "" if there is an error.

Is this what you want to do?

Here is the formula so far if you want to copy/paste (note - you will need to set up the cross sheet references locally)

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), "")

Now, if you want to check another Index sheet if there is no match in the first one you can replace the "" we added for No Match with a repeat of the formula, but with cross sheet references to your second index.

Changing the first reference to the second sheet

And the second reference

The formula is now

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)), ""))

Now, if the reference is not found in the first sheet, it will look in the second one.

You can extend this for additional index sheets in the same way to look in a third sheet (addition is in bold):

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)),

IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 3 RCW}, MATCH([Data source]@row, {INDEX sheet 3 Data source}, 0)), "")))

You can use the same process to create a formula for the other two columns, just change the first sheet reference (the one in the index) in each of your INDEXES.

=IFERROR(INDEX({INDEX sheet RCW}, MATCH([Data source]@row, {INDEX sheet Data source}, 0)), "")

Does that achieve what you want?

ljkeefe1

@KPH Ok, we are starting to make some progress! It mostly works. I copied the equation down the column. It's working for most cells, except for the ones towards the bottom of the sheet, and I did get back some "incorrect argument set" errors.

KPH

The Incorrect Argument Set error means there is probably a comma missing (or one extra) within the formula. The fact it only occurs on some rows means it is only a problem on the parts of the formula where the preceding IFs have been false.

For example, this formula will fail only if there is no match found against Sheet 1 and Sheet 2 as the error (the comma in bold on the last row) is after the point where the formula would stop being evaluated if a match had been found.

=IFERROR(INDEX({INDEX sheet 1 RCW}, MATCH([Data source]@row, {INDEX sheet 1 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 2 RCW}, MATCH([Data source]@row, {INDEX sheet 2 Data source}, 0)),

IFERROR(INDEX({INDEX sheet 3 RCW}, MATCH([Data source]@row,, {INDEX sheet 3 Data source}, 0)), "")))

So, the first step in troubleshooting would be to look at the last part and work backwards. You can split the formula up and test each in turn, if that is easier.

ljkeefe1

@KPH I missed the comma and the quotation marks to close out the formula. ☺️ Now it works!! Thank you so much for your help! I really appreciate the time you took to break it down for a newbie like me 😁

KPH

Excellent news, well done!

ljkeefe1

@KPH Quick question - Do I need to do anything special to the formula if I'm applying it to a column with a dropdown list?

KPH

Maybe 😉

If it is a single select dropdown you don’t need to do anything.

If it is a multi select and you are looking to match something that is in a cell with other things you will need to use another function.

ljkeefe1

@KPH Got it. Thank you! 😊