Re: #VALUE error with an IF and FIND formula.

"u473" <u...@xxxxxxx> wrote...
I am given a material quantity spread*** in wich the Item Type
is buried in the Item Description as follows :

. Colum A Column B
Row 1 Item Description Quantity
Row 2 blablabla ELBOW blabla 2
Row 3 blabla TEE blablablabla 1
Row z

I tried to add Columns C & D to be populated with the quantities
of Elbows or Tees by using an IF and FIND formula as follows
for Cell C2 = IF(FIND("ELBOW",A2),B2,0)
But I get a #VALUE error if my test string is not found, whereas I
just want 0 (zero) if the test string is not found.
....

FIND is case-sensitive, so FIND believes "XyZ" doesn't appear in "foo
xYz bar". If that's what you want, try

=IF(FIND("ELBOW",A2&"ELBOW")<LEN(A2),B2,0)

If you don't need case-sensitivity, try

=IF(COUNTIF(A2,"*ELBOW*"),B2,0)

.