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 have three statements:

=IF(ISNUMBER(SEARCH($ET$2,B2)),B2,CONCATENATE("NA ",B2))

But I want to do

=IF(ISNUMBER(SEARCH($ET$2 **OR** $ET$3 **OR** $ET$4, B2,CONCATENATE("NA ",B2))

how could this be accomplished in Excel?

or could I instead nest these two (which I have tried, but had no luck)

=IF(ISNUMBER(SEARCH($ET$2,B2)),B2,CONCATENATE("NA ",B2))
and
=IF(ISNUMBER(SEARCH($ET$2,B2)),B2,CONCATENATE("NA ",B3))
share|improve this question

migrated from stackoverflow.com Jun 22 '12 at 18:44

This question came from our site for professional and enthusiast programmers.

    
Not a programming question. Should be asked at Super User –  brettdj Jun 21 '12 at 10:18
add comment

3 Answers 3

up vote 0 down vote accepted

Your question is not very clear, but that would include an or condition in the if statement:

=IF(OR(ISNUMBER(SEARCH($ET$2,B2)), ISNUMBER(SEARCH($ET$3,B2)), ISNUMBER(SEARCH($ET$4,B2))),B2,CONCATENATE("NA ",B2))

note: in Excel2003, I am not sure that OR can take more than 2 arguments, in which case you can do OR(OR(x,y),z)

share|improve this answer
add comment

Not very readable, but:

=IF(OR(ISNUMBER(...), ISNUMBER(...),ISNUMBER(...)), B2,CONCATENATE("NA ",B2))

should do it.

share|improve this answer
add comment

To shorten the formula you could use this version

=IF(SUMPRODUCT(ISNUMBER(SEARCH($ET$2:$ET$4,B2))+0),"","NA ")&B2

....or even shorter using COUNT but it needs to be "array entered"

=IF(COUNT(SEARCH($ET$2:$ET$4,B2)),"","NA ")&B2

share|improve this answer
add comment

Your Answer

 
discard

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