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

Options
✭✭✭✭✭

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.

## Answers

• ✭✭✭✭✭✭
Options

You would put the RT 1 range/criteria set in the COLLECT function.

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

• ✭✭✭✭✭
Options

That works. How would I add multiple INDEX(COLLECTS into the same formula?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!