2

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?

3
  • Are you trying to do multiple comparisons, test to see if two literals are equal, or something else?
    – soandos
    Jan 18, 2012 at 4:59
  • Yes, just doing string comparisons.
    – hubbas
    Jan 18, 2012 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. Jan 18, 2012 at 5:24

3 Answers 3

2

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
","")
3
  • I can not thank you enough! This is exactly what I was looking for!
    – hubbas
    Jan 18, 2012 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, 2012 at 5:48
  • Yes, put the new line character inside the result instead of after each test. See edits above. Jan 18, 2012 at 6:05
2

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

1

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

You must log in to answer this question.

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