How to correctly use INDEX(COLLECT())

I am trying to create a cross-sheet formula that pulls the data from another sheet if it meets 3 criteria.

In the source sheet, I need the following criteria met:

1. "Month/Year" column to be the same as the "Month/Year of Report" column in my target sheet.

2. "Activity code" column to be "2105532"

3. "Name" column to be "Sophie Knudson"

I then want the cell within the "Hours" column in the source sheet that meets all of that criteria to be automatically populated into my target sheet.

Could someone please help me with creating this formula? This is what I've tried and I keep getting #UNPARSEABLE messages.

=INDEX(COLLECT({Source Sheet Range 5}, {Source Sheet Range 2}, [Month/Year of report]@row, [{Source Sheet Range 3}, "Sophie Knudson", {Source Sheet Range 4}, "2105532"]))

Your support is greatly appreciated.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!