In this video I answer a question from the Smartsheet Community. A user is trying to make a formula that will evaluate a State in a cell and return the region that State belongs to.
Watch as I create the entire Sheet and formula from the ground up.
In this situation, a user wants to select one of nine states and return the appropriate region the state belongs to.
The states and their respective region are as follows:
- NY, NJ, CT = Northeast
- NC, SC, GA = Southeast
- CA, AZ, NV = West
The process to use this sheet is to select a State in the State column. Based on the selection, the appropriate value in the Region column populates.
To accomplish this, a formula needs to be made in the Region column that references the State cell of the same row and based on the State returns a Region.
The screenshot below shows the solution to this problem.
The formula is below:
=IF(OR(State@row = "NY", State@row = "NJ", State@row = "CT"), "Northeast", IF(OR(State@row = "NC", State@row = "SC", State@row = "GA"), "Southeast", IF(OR(State@row = "CA", State@row = "AZ", State@row = "NV"), "West")))
