Nested IF statements with exact OR values

Question

I'm new to Excel IF statements and am having trouble with what I believe is called a nested IF function. I've looked at other IF questions on here and they're too complicated for my novice brain to understand.

I have a column that has numbers in it. There are about a dozen different numbers. The numbers represent a specific team. For example, 100 is team red, 101 is team yellow, 102 is team green, etc. I need to create an IF statement that will tell me what each of the teams are based on the numbers. I have hundreds of rows to filter through and I don't want to do them manually if the function exists to automate it in Excel.

I tried to write my own nested IF statement and it's not working. What am I doing wrong?

=IF(B2="100",red,IF(B2="101",blue,IF(B2="103",green,IF(B2="104",yellow,""))))

Help?

Answer 1

Hi I don't have access to excel to test this as I am not using windows but try the following it should work:

=IF(B1=100,0,IF(B1=101,1,IF(B1=102,2,IF(B1=103,3,IF(B1=104,4)))))

from the above you can say 0 represents Red 1 represents blue and so on

Answer 2

Your cells contain numbers but you are treating the numbers as text in your formula.

Better alternatives to nested IFs are VLOOKUP or CHOOSE like so:

=VLOOKUP(A1,{100,"Red";101,"Blue";102,"Orange"},2)

=CHOOSE(A1-99,"Red","Blue","Orange") 

Answer 3

Nested IF statements are definitely mind-bending, and at least in my experience I still get confused :) You also run into an issue with certain versions of Excel where the number of nested IF's you are allowed is limited. In conjunction with that, they become difficult to debug the more levels of nesting that are added. Regarding your current formula, @DaveSexton's suggestion of changing the strings to numbers (removing the quotes) should help. Also, make sure that you are quoting your color values in the formula, because using red (and not "red") will cause a #NAME? error (assuming you haven't defined red already).

In your situation, have you thought about trying a VLOOKUP formula? Instead of hard-coding the team names in the formula itself, you would set up a table that would have a team number and the corresponding team name:

The formula here (in column B2) is:

=VLOOKUP(A2,$D$2:$E$6,2,FALSE)

What this does is looks at cell A2 and then goes to the table in range D2:E6, looking for the value of A2 in column D. When it find it, it returns the value in column E, which would be the team name.