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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!