Index Collect Formula Assistance

Tonya Baut

I want to lookup a specific start date based on 2 criteria: (1) Workset; (2) Week # in the main sheet. I have a separate sheet that contains the dates I am pulling in.

I tried using INDEX(COLLECT and I can't seem to get it to work.

Data Set:

Workset Week #

A, B, C, D 1-12

Example: If you have Workset A and Week # 5 the date would be 6/30/25. Workset D and Week #5 would return 3/9/26.

Here is the formula I was trying to use:

=INDEX(COLLECT([Workset Start Week Date]1:[Workset Start Week Date]48, Workset1:Workset48, Workset@row, Week Number1:Week Number 48, Week Number@row),1

Any help is much appreciated, thank you!

jmyzk_cloudsmart_jp

Hi @Tonya Baut

I am not sure what you meant by the Week Number, as your examples are neither the WEEKNUBER() nor WEEKDAY().

I used the formula below in my demo solution to get the set from the Sheet Summary fields.

=IFERROR(
  INDEX(
    COLLECT([Workset Start Week Date]:[Workset Start Week Date], 
              Workset:Workset, Workset#, 
              [Week Number]:[Week Number], [Week Number]#
    )
  , 1)
, "")

In your formula, you miss a ")" at the end.

=INDEX(COLLECT([Workset Start Week Date]1:[Workset Start Week Date]48, Workset1:Workset48, Workset@row, Week Number1:Week Number 48, Week Number@row),1

https://app.smartsheet.com/b/publish?EQBCT=30e58e663dea44c6b87136aacc8abe47