Index Match/Collect/Join?
I'm struggling with the right syntax for the formula I need and would love some help.
On my reference sheet I have a list of countries in a column and the list of various entities across the top. Then I have the names of the people who are responsible for each country (in total I have 195 countries).
On my main sheet I am collecting various information via a form, and most importantly, I'm collecting Country and Entity. Based upon the country and entity, I want to use a formula to populate the Responder.
Any genius ideas how to do this?
Thanks in advance!
Best Answer
-
Hi @Leibel Shuchat - this didn't work. But I figured it! I need an "IF" statement with my index match. This is what I came up with:
=IF([Entity]@row = "ABC", INDEX({ABC}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "XYZ", INDEX({XYZ}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "123a", INDEX({123a}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "789z", INDEX({789z}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "Don't Know", INDEX({DontKnow}, MATCH(Country@row, {Country1}, 0)))))))
It took me a bit but finally got it!
Answers
-
See below. Name cross sheet references as needed
=INDEX({Reference sheet - responders}, MATCH(Country@row, {Reference sheet - Countries}, 0), MATCH(Entity@row, {Reference sheet - Entities}, 0))
-
Hi @Leibel Shuchat - this didn't work. But I figured it! I need an "IF" statement with my index match. This is what I came up with:
=IF([Entity]@row = "ABC", INDEX({ABC}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "XYZ", INDEX({XYZ}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "123a", INDEX({123a}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "789z", INDEX({789z}, MATCH(Country@row, {Country1}, 0)), IF([Entity]@row = "Don't Know", INDEX({DontKnow}, MATCH(Country@row, {Country1}, 0)))))))
It took me a bit but finally got it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!