0

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))
1
  • Not a programming question. Should be asked at Super User
    – brettdj
    Jun 21, 2012 at 10:18

3 Answers 3

0

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)

1

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

0

Not very readable, but:

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

should do it.

You must log in to answer this question.