Help with formula producing #Value!

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

The following formula is producing #VALUE! -- any ideas why?

=IF(FIND("VCC",B6,1),"VCC",IF(FIND("DCC",B6,1),"DCC","REG"))

Tks in advance...
 
JMay,

Form excel online help

· If find_text does not appear in within_text, FIND returns the #VALUE
error value.
· If start_num is not greater than zero, FIND returns the #VALUE! erro
value.
· If start_num is greater than the length of within_text, FIND return
the #VALUE! error value.

Try

=IF(NOT(ISERROR((FIND("VCC",B6,1)))),"VCC",IF(NOT(ISERROR(FIND("DCC",B6,1))),"DCC","REG"))
This formula tests fro the # value error and returns true/false for th
found result
 
FIND returns #VALUE! whenever it doesn't find the
find_text string within the within_text value, and
therefore can't produce the starting number of
find_text.

A better way to use FIND for your purpose might be to
combine it with the ISERR function:
=IF(iserr(FIND("VCC",B6))=FALSE,"VCC",IF(iserr(FIND
("DCC",B6))=FALSE,"DCC","REG"))

The Excel Help file has some good examples for you as
well.

Jody
 
Back
Top