INDEX COLLECT formula
I am having some issues with the index collect. I am trying to return data from one sheet looking at two columns from another. I have watched multiple videos on this and I cannot seem to get it right.
Currently I have: =INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))
Asset ID is on sheet 2 column 3
Maker Codes is on sheet 2 column 1 rows 1-8
Maker Line is on sheet 1 which user inputs via form
Packer Code is on sheet 2 column 1 rows 9-16
Packer Line is on sheet 1 which user inputs via form
I can get this to work using index match but I need it to look at both columns on sheet 1 as it is going to be either a Maker Line or Packer Line. =INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))
Sheet 2---^
Sheet 1---^
Answers
-
Hi @REmley ,
I can't tell from your example where the Packer Line is referenced in Sheet 2, or how you are expecting these to match. Can you give another example with screenshots of them matching? And in which field you need your index/collect to go?
Thanks, Ryan
-
=INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))
I have selected rows 1-8 for Maker Code on sheet 2 and selected rows 9-16 for Packer Code.
We have a form that gives the option of Maker, Packer or Other. If maker is selected they get codes A-H and if packer is selected they get codes 1-8. Depending on what they sheet 1 will get filled in the corresponding column. What I am trying to do is use index collect to recall the Asset ID while searching in both Maker Line and Packer Line.
=INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))
Works fine and will recall the Asset ID but it is only based on the Maker Line and if they chose a Packer Line nothing will be recalled. Does that make sense?
-
@REmley I would probably check if the Maker Line field is blank, and if it is check the Packer code. something like:
=if(ISBLANK({packer code}), (INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))), (INDEX({Asset ID Code}, MATCH([Packer Line]@row, {Packer Code}, 0))))
(Sorry, haven't tested this across two sheets, but I think the logic is okay.)
dm
-
@Dale Murphy - Thank you for the input, however, I was still unable to get it to work with your suggestion. I final got the formula to work as I had the syntax wrong at the ending.
=INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))
The ending syntax should of looked like [Packer Line]@row), 1)
Sorry for the late response, I was out for a surgery!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!