• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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?


1 Solution
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...
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
> =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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now