# Excel: Multiple =IF calculations within one cell

I need to do some text comparisons in excel, about 20-50 of them. I would like the result of these if statements to be within a single cell. Here is how I WISHED it would work:

=IF("Test1"="","Equal1","Not equal1")

=IF("Test1"="","Equal1","Not equal1")

=IF("Test1"="","Equal1","Not equal1")

=IF("Test1"="","Equal1","Not equal1")

If that did work, I would like to see the following in the cell:

Not Equal1

Not Equal1

Not Equal1

Not Equal1

Is there any way, maybe using some advanced method, to calculate multiple string comparisons like this, all within a single cell?

-
Are you trying to do multiple comparisons, test to see if two literals are equal, or something else? –  soandos Jan 18 '12 at 4:59
Yes, just doing string comparisons. –  hubbas Jan 18 '12 at 5:02
Your question is not very clear to me but it may be possible using an array formula. For example, `={SUM(IF(A1:A4<>B1:B4,1,0))}` will return the number or rows (1-4) where column A <> Column B. If you can make your question more clear I might be able to provide an answer. –  Mike Fitzpatrick Jan 18 '12 at 5:24

## 3 Answers

Type new line characters betwen each if statement.:

``````=IF("Test1"="","Equal1","Not equal1")&"
"&IF("Test2"="","Equal2","Not equal2")&"
"&IF("Test3"="","Equal3","Not equal3")&"
"&IF("Test4"="","Equal4","Not equal4")
``````

EDIT: Or to put new lines only after affirmative tests:

``````=IF("Test1"="","Equal1
","")&IF("Test2"="","Equal2
","")&IF("Test3"="","Equal3
","")&IF("Test4"="","Equal4
","")
``````
-
I can not thank you enough! This is exactly what I was looking for! –  hubbas Jan 18 '12 at 5:27
One other question, it it possible to remove the new line characters on some of the if statements depending on their result? –  hubbas Jan 18 '12 at 5:48
Yes, put the new line character inside the result instead of after each test. See edits above. –  Hand-E-Food Jan 18 '12 at 6:05

You can just do a series of `IF`s within a concatenation to get them into the same cell:

``````=CONCATENATE(IF("test"="test","yes","no"),IF("test"="test2","yes","no"))
``````

You'll probably want some spacing in there to make it look pretty, or a CHR(10) to get new lines (turn on wrap on the cells).

-

Are you talking about nested-if? Where you have multiple if statement in a single cell. If so then check this out http://spreadsheets.about.com/od/tipsandfaqs/qt/nested_if.htm

-