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)
- Columns
- SD Tracking (sheet with formulas)
- Columns
- Site ID
- RT Sticker (where the formula lives)
- Columns
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
-
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)
-
That works. How would I add multiple INDEX(COLLECTS into the same formula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!