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

Justin Mauzy

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.

Paul Newcome

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)