If statement only shows value in first cell

[Mea]

I'm trying to use an IF statement to find a value in a row array, and return
that text value in the formula cell.

1 a b c d e
2 101 INDUSTRIES brass
3 LIGHTING HOME nickel
4 FRANKFURT brass nickel stainless copper
5 Jersey copper
6 Dallas nickel stainless cooper brass


{=IF(b2:e2="brass,"brass",0)}
next cell down would have {=IF(b3:e31="brass,"brass",0)}

If "brass" is in the first cell of the array, it returns "brass". If it in
in cells c2,d2,or e2, it returns a 0. What am I doing wrong?

 

[Gary''s Student]

If you have a group of cells and want to know if some text is in ANY of them,
use COUNTIF:

=IF(COUNTIF(B2:E2,"Brass")>0,"Brass",0)

 

[ShaneDevenshire]

Hi,

You can use this short version:

=IF(COUNTIF(B3:E3,"brass"),"brass",)

 

[Ashish Mathur]

Hi,

You may also try this array formula

=if(or((a3:b3="brass"),"brass",""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com