Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.