Once I select a country from drop down, I want the region to be auto populated in the next column.

Ram D

Basically if I select a country/group of countries from drop down in one column, I want the region/s to be auto populated in the next column. Is there a way to achieve this?

Danielle O'Connell

You can use an IF statement to achieve this, but it's going to take a little work on your part to create depending on how many countries and regions you have. Here's an example:

Column 1 = dropdown of countries Canada, USA, Mexico

Column 2 = regions (can also be a dropdown, multiselect, text, etc) - for simplicity lets say the regions are east, west, north, south

In Column 2 you can use this statement (obviously modified to fit what you want your region / country associations to be)

=IF([Column 1]@row = "Canada", "North", IF([Column 1]@row = "USA", "south", IF([Column 1]@row = "Mexico", "east, west", "")))

With the formula above you will see North in column 2 when Canada is selected, South if USA is selected, and East, West if Mexico is selected. You can just keep doing more IF statements in the "false" value until you have all your countries and regions. The very last false statement within the IF formula you can do "" for a blank or "n/a", etc. It should only appear if none of your other statements are true (IE the cell is blank, a new country is added, etc).

Andrée Starå

Hi @Ram D

I hope you're well and safe!

To add to Danielle's excellent advice/answer.

Depending on how many options there can be, I would recommend a VLOOKUP or INDEX/MATCH structure in a section of the sheet or combined with cross-sheet formulas in another sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Danielle O'Connell

You can use an IF statement to achieve this, but it's going to take a little work on your part to create depending on how many countries and regions you have. Here's an example:

Column 1 = dropdown of countries Canada, USA, Mexico

Column 2 = regions (can also be a dropdown, multiselect, text, etc) - for simplicity lets say the regions are east, west, north, south

In Column 2 you can use this statement (obviously modified to fit what you want your region / country associations to be)

=IF([Column 1]@row = "Canada", "North", IF([Column 1]@row = "USA", "south", IF([Column 1]@row = "Mexico", "east, west", "")))

With the formula above you will see North in column 2 when Canada is selected, South if USA is selected, and East, West if Mexico is selected. You can just keep doing more IF statements in the "false" value until you have all your countries and regions. The very last false statement within the IF formula you can do "" for a blank or "n/a", etc. It should only appear if none of your other statements are true (IE the cell is blank, a new country is added, etc).

Andrée Starå

Hi @Ram D

I hope you're well and safe!

To add to Danielle's excellent advice/answer.

Depending on how many options there can be, I would recommend a VLOOKUP or INDEX/MATCH structure in a section of the sheet or combined with cross-sheet formulas in another sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Danielle O'Connell

@Andrée Starå great point! I second that recommendation!

Ram D

Hello Danielle and Andree,

Thanks for the help and prompt response. It really helped me to understand how regions can be auto populated.

But there is one more dimension to it and if you can help me with either "If statement" or "Vlookup formulae", it will be really great.

If there are three countries selected in a particular cell of first column (e.g., A2 has USA, Mexico and Canada) and values in B2 should be (North, West and South). Can you please help me with formulae on how all the 3 regions in B2 can be auto populated in a single cell : North,West and South.

The ask here is for populating multiple values in a single cell.

Thanks once again!!

Danielle O'Connell

@Ram D this gets a little trickier depending on how many values you anticipate having. Here is how you can do it via the IF statement:

ColumnA = USA, Mexico, Canada as choices and cell A2 has all 3 selected

ColumnB = North, South, East, West and you want North, West, South to appear in cell B2

=IF(AND(CONTAINS("USA", [ColumnA]@row), CONTAINS("Canada", [ColumnA]@row), CONTAINS("Mexico", [ColumnA]@row)), "North, West, South", "")

To then have all your different combinations you could replace the "" with the next IF statement (IE contains USA and Canada, followed by USA and Mexico, followed by Canada and Mexico, followed by USA alone, followed by Canada alone, followed by Mexico alone).

When I have long IF formulas I usually try to have the most complex scenario (IE all three countries being selected) and work down to the simplest (IE 1 country being selected). As you can see this could make a lengthy formula (4000 character limit) and could open you up to error if you miss something when you original write it.

For VLOOKUP, you would have to make a buddy grid or columns in your grid with all the possible combinations. Basically one column would have your country combos and the column to the right all the regions. Then it would be a simple

=VLOOKUP([ColumnA]@row, {buddy grid country column range to regions},2,false)

Both require some work to setup. The VLOOKUP might be easier in the end just due to visually being able to see that you have all your combos in a column rather than having to read through a lengthy formula to be sure you didn't miss anything. The VLOOKUP method also would be easier to update if you have new countries or regions to add.

Hope that helps :)