Forum Discussion

WilliamR465's avatar
WilliamR465
Copper Contributor
Oct 15, 2024

Nested if functions

Hi guys, im trying to do a nested if function revolving around this : =IF(F2="less than high school",1, IF(F2="high school",2,IF(F2="associates",3,IF(F2="bachelors",4,IF(F2="masters",5, IF(F2="more than masters",6,))))))

 

it shows me 3,4 and 5 but shows a 0 for 1,2 and 6. any ideas on how to fix this?

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    WilliamR465 

    I would use SWITCH for this because the logic reads easier:

    =SWITCH(
        F2,
        "less than high school", 1,
        "high school", 2,
        "associates", 3,
        "bachelors", 4,
        "masters", 5,
        "more than masters", 6,
        "None"
    )
  • WilliamR465 

    Text comparison in Excel is (in most situations) case-sensitive. So if F2 contains High School, the formula won't return 2 since High School is not equal to high school.

    Also, an extra space will throw a spanner into the works.

    Try this:

     

    =XMATCH(TRIM(LOWER(F2)), {"less than high school", "high school", "associates", "bachelors", "masters", "more than masters"})

Resources