Search

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

797 views0 comments