Multiple Index Collect statements when a value is matched on another sheet.

Here are the sheets/columns that I am dealing with:

  • SD Form (external sheet)
    • Columns
      • Site ID (SD Form Range 1)
      • RT 1 (SD Form Range 3)
      • RT 2 (SD Form Range 4)
      • 1 Sticker (SD Form Range 2)
      • 2 Sticker (SD Form Range 6)
  • SD Tracking (sheet with formulas)
    • Columns
      • Site ID
      • RT Sticker (where the formula lives)

Here is what I am trying to accomplish:

  • Look at the RT 1 column on the SD Form sheet to match a value. In this case it would be 16.
  • If it is a match, then pull the value from 1 Sticker column on the SD Form sheet if the (2nd match) the 2 Site ID columns match.
  • As a side note, I need to be able to add this to look at 15 different columns.

Here is the formula that I am getting an #INVALID OPERATION error.

=IF({SD Form Range 3} = 16, INDEX(COLLECT({SD Form Range 2}, {SD Form Range 1}, [Site ID]@row), 1), IF({SD Form Range 4} = 16, INDEX(COLLECT({SD Form Range 6}, {SD Form Range 1}, [Site ID]@row), 1)))

So a quick recap:

I need to look at an external sheet to see if value is there (in this case 16). Then collect a value if the Site ID's match.

Thanks in advance.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!