Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
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
up vote 2 down vote accepted

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
","")
share|improve this answer
    
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 IFs 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).

share|improve this answer

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .