Forum Discussion

RebeccaA's avatar
RebeccaA
Copper Contributor
Oct 31, 2019

Nested "if" Excel 2007

Losing. My. Mind.

 

I need to assign a region to each row of a spreadsheet based on the state listed in it.

Let's say B1 is the state name.

A1 needs to sort those states into North, South, East, West.

Formula:

=IF(B1=California,"West",IF(B1=New York,"East",IF(B1=Minnesota,"North",IF(B1=Texas,"South"))))

(Hugely simplified for this post).

The result is: #NAME?

Where am I going wrong?

 

Formulas and Functions

4 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Oct 31, 2019

    Hi RebeccaA 

     

    Your formula is correct you simply missed out "". Please use below

     

    =IF(B1="California","West",IF(B1="New York","East",IF(B1="Minnesota","North",IF(B1="Texas","South"))))

     

    Thanks

    Tauqeer

    • RebeccaA's avatar
      RebeccaA
      Copper Contributor
      Oct 31, 2019
      Unbelievable. All these hours and that was it. Thank you. THANK YOU!!!
      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Oct 31, 2019
        Another CHOICE would be:
        =CHOOSE(MATCH(B1,
        {“California”,”Minnesota”,
        “New York”,”Texas”},0),
        “West”,”North”,”East”,”South”)
        LOOK no further, I prefer:
        =LOOKUP(B1,
        {“California”,”Minnesota”,
        “New York”,”Texas”},
        {“West”,”North”,”East”,South”})

Resources