Index Match/Collect/Join?

jtl

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!

jtl

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!

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

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!