Excel: Nested If, Then Statement


I have an Excel column containing 2s and 3s.  I tried creating a "nested If, Then statement" to say If D2 = 2, then make it 3 and if D2 = 3, then make it 2.  But, my formula produces the word "FALSE" instead of a number:

=IF(D2="2", "3", IF(D2="3", "2"))

How do I fix this statement to make it how I need?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
=IF(D2=2, 3,2)

Open in new window

TBSupportAuthor Commented:
Hi Randy:

Thanks, for the quick response.  But, that produces "2" and not "3".

Randy PooleCommented:
I have this formula on Cell E2, if I enter 2 in cell D2 then E2 shows 3, if I enter 3 in cell D2, E2 shows 2...
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

let's take this apart. Your formula is

=IF(D2="2", "3", IF(D2="3", "2"))

Open in new window

and you say that the result is "False".   This means that D2="2" is false, so the 'false' part of the outer IF statement is processed, which is the inner IF statement IF(D2="3", "2")). Here you check if D2="3". The formula only has a value for the 'true' part and nothing for the 'false' part. Since Excel returns "FALSE" it means that the comparison D2="3" is false. If Excel finds no entry for the 'false' part of the IF function, it will return "FALSE".

So, D2 is neither "2" nor "3".  Please have a closer look at D2. What does it actually contain? In your formula you test for "2" and "3". These are both text values. Does the cell contain text or numbers? Do you WANT to test for text or for numbers?

If the cell contains text, is the "2" or the "3" all that there is in the cell? There may be a space character leading or trailing, so that the real value is "2 " or "3 ". Select the cell, hit F2 and play with the right and left arrow keys. How many different positions can the cursor take up before and after the number? A space is not a visible character, but it will feature in a comparison.  The text "2" is not equal to "2 " or " 2" or " 2 ".

If the cell contains a number, then the test in the IF statement is the wrong test. You can test for a number by just entering the number, without the quote characters, as Randy suggested in the formula =IF(D2=2, 3,2)

But you say that this returns 2 and not 3, which means that D2 is not the numeric value 2 either.

So, analyse your cell D2 and adjust either the cell value or your comparison value to get the result you expect.

If D2 contains leading or trailing spaces you can use the TRIM() function to disregard these in the IF statement.

=IF(Trim(D2)="2", "3", "2")

Open in new window

This will work for the regular space character, but not for the non-breaking space that is used on web pages and may be in the cell if the value has been copied and pasted from a web site.

If all this does not help, please post a sample file that shows the problem.

cheers, teylyn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
> =IF(D2="2", "3", IF(D2="3", "2"))

You need two commas per IF. So you are missing last 'ELSE' before the double parens

=IF(D2="2", "3", IF(D2="3", "2","?"))

>  if I enter 2 in cell D2 then E2 shows 3, if I enter 3 in cell D2

Should work for that.
Question remains, what if you enter something other than a 2 or a 3?
Maybe this is better:

=IF(D2="2", "3", IF(D2="3", "2"," "))
=IF(D2="2", "3", IF(D2="3", "2",""))
This also works, but produces little -> what else do you want to do?
Where this is an attempt to just end up switching two values already present in column D, then use

=IF(D2="2", "3", IF(D2="3", "2",D2))

and the other values {1,4,5,6...a,b,c,...} will remain in place
- and if so, heed teylyn comment and ensure values present in sheet that are supposed to be common really are - clean it up with functions like trimming, ensure all cells are formatted to text, or whatever.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

>> You need two commas per IF.

No you don't. The false argument can be omitted. You just need to understand the implications.

>> what if you enter something other than a 2 or a 3?

According to the question that is not a concern.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.