Multiple Index Collect in same formula
I need to be able to put 2 Index Collect in the same formula.
This formula works fine but I need to be able also look for July (7) and Index another external reference.
{Site Information Sheet Range 2} = Date column on external sheet
{Site Information Sheet Range 3} = Text/Number column on external sheet
=INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 8), 1)
I have tried using IF statements and get an error.
=IF(INDEX(COLLECT({Coil Site Information Sheet Range 2}, {Coil Site Information Sheet Range 3}, [Site ID:]@row, {Coil Site Information Sheet Range 2}, MONTH(@cell) = 7), 1), IF(INDEX(COLLECT({Coil Site Information Sheet Range 2}, {Coil Site Information Sheet Range 3}, [Site ID:]@row, {Coil Site Information Sheet Range 2}, MONTH(@cell) = 8), 1)))
Thanks in advance for the help.
Best Answer
-
I figured it out. I had to add a helper column on the local sheet called Month. I then entered the month for example March would be 3, April 4 and so on... Using IF, INDEX, COLLECT then worked.
Here is the working formula:
=IF(Month@row = "4", INDEX(COLLECT({Site Information Sheet Range 2}, { Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 4), 1), IF(Month@row = "5", INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 5), 1)))
Answers
-
I figured it out. I had to add a helper column on the local sheet called Month. I then entered the month for example March would be 3, April 4 and so on... Using IF, INDEX, COLLECT then worked.
Here is the working formula:
=IF(Month@row = "4", INDEX(COLLECT({Site Information Sheet Range 2}, { Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 4), 1), IF(Month@row = "5", INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 5), 1)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!