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

  • jtl
    jtl ✭✭
    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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @jtl

    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))

  • jtl
    jtl ✭✭
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!