Forum Discussion
WilliamR465
Oct 15, 2024Copper Contributor
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?
- Patrick2788Silver Contributor
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" )
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"})