Index/Collect Formula Not Working..Incorrect Argument Set Error

cibfit

I have two sheets.

Sheet A is a rate sheet with the columns: CEB, Client Name, Client Engagement Name, Engagement Resource, Engagement Rate.

Sheet B is my weekly billable time report with the columns: Quarter, CEB, Client Name, Project Name, Date, Hours, Employee, Description, Rate, Total Cost

I want to pull over Engagement Rate over from Sheet 1 to Sheet 2 if CEB, Client Name, Project Name, and Employee Match.

Here is the formula

=INDEX(COLLECT({RateEngRate}, {RateCEB}, CEB@row, {RateClientName}, [Client Name]@row, {RateEngName}, [Project Name]@row, {RateEE}, Employee@row))

This results is INCORRECT ARGUMENT SET. What am I doing incorrectly?

Thank you in advance for the help!

Kelly P.

@cibfit

There's a missing piece at the end of the formula - the index portion needs a row index. Try this as a first step toward resolving the error:

=INDEX(COLLECT({RateEngRate}, {RateCEB}, CEB@row, {RateClientName}, [Client Name]@row, {RateEngName}, [Project Name]@row, {RateEE}, Employee@row), 1)

See this article for more information:

https://help.smartsheet.com/function/index

Hope this helps!

cibfit

@Kelly P. Thank you for responding so quickly. I am now receiving an INVALID VALUE Error. Thoughts?

Kelly P.

@cibfit

Hmmm...that is strange. I tested the formula and it's working for me. Perhaps send some screenshots?

cibfit

@Kelly P. Here is a sample of the data for Sheet 1 and Sheet 2

SS Sheet 1.png

SS Sheet 2.png

Kelly P.

@cibfit

In your formula, you have {{RateEE}} and Employee@row. Which column in sheet 1 corresponds to RateEE? Is it the Engagement Resource? Will the values in this column be unique? The other referenced columns seem to have the same values as one another. If Engagement Resource also has duplicates, you won't be able to make a unique match.

Another approach to this formula that you may find simpler would be to add a column to each sheet called "UniqueID" which would join all of the columns you want to match into a single column. It needn't be pretty (e.g., you can have, for instance, a value of "CHEM11-013-001Chemonics International IncDigital HR Strategy & Solution Selection SupportUberChristen" for the first row in sheet 1. In each sheet, these new columns can be hidden. Then you can use a simple index/match formula: =INDEX({RateEngRate}, MATCH(UniqueID@row, {RateUniqueID}, 0))