Smartsheet Formula Tutorial | Use nested IF/OR statement to return a region based on state.

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:

  1. NY, NJ, CT = Northeast
  2. NC, SC, GA = Southeast
  3. 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")))

Unlock the Full Potential of Smartsheet

Focus on growing your business while our Smartsheet experts handle the rest. Book Your Discovery Call today!

Strategic Plan Dashboard Image.