Collect / Index / Match
My brain is fried so I'm hoping someone can help. I have the following {wrong} formula:
=COLLECT({Max Headcount}, INDEX({Position}, MATCH([Primary Column]@row, {Position}, 0)))
On this sheet, I have a list of positions in the primary column. On the referenced sheet, I have the same list of positions and their max headcount. I'd like it to cross reference the position and, if the same, report the max headcount.
Easy enough, right?
Best Answer
-
Hey @Nick Allgauer
If I understand correctly, you would like to find the Max Headcount using Position on both sheets as the match. Assuming I got it right, we don't need to use Collect since we only have one criteria (Position) to match. If there were multiple criteria, then Collect might be necessary.
Try this.
=INDEX({Max Headcount}, MATCH([Primary Column]@row, {Position}, 0))
cheers,
Kelly
Answers
-
Hey @Nick Allgauer
If I understand correctly, you would like to find the Max Headcount using Position on both sheets as the match. Assuming I got it right, we don't need to use Collect since we only have one criteria (Position) to match. If there were multiple criteria, then Collect might be necessary.
Try this.
=INDEX({Max Headcount}, MATCH([Primary Column]@row, {Position}, 0))
cheers,
Kelly
-
Perfect, thank you! It's been a long week. 😀 😀 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!