Need Help with INDEX(COLLECT w/ CONTAINS
Hi All,
I'm trying to populate a column with a global Region value (AMER, APJ, EMEA) based on a local region code contained in an appliance name.
I have two sheets with data.
Sheet 1 contains a column of appliance names and a second column for the Region I want it to populate.
Sheet 2 has two columns, 1 for the Data Center Code (SMF, AMS, etc) and the second with the global Region (AMER, EMEA, etc)
I would like it to populate the global region into sheet 1 based on the Data Center Code contained in the appliance name.
I've tried a few of the other posts n the forum and feel like I'm close but I cant get it to work right.
Best Answer
-
The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said
=Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said
=Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks @Mark.poole. That helped alot. I also found there was some issues with the data itself causing matching errors. But I got it to work! Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!