[EarlyBirdie]

Cell A1 contains the value "Insurance Claim" and Cell A2 contains the value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance". The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No Claim"
and "Claim" in the respective cells. However, when I change the formulas to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display "No
Claim" for all three cells. I expected to see the same results as generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[Kevin B]

ISLOGICAL evaluates a cell and returns TRUE if the cell contains TRUE or
FALSE, and FALSE for anything else. ISERROR returns a logical value, but if
your FIND function returns an error ISLOGICAL will return FALSE because and
error is neither TRUE or FALSE.
--
Kevin Backmann


"EarlyBirdie" wrote:

Cell A1 contains the value "Insurance Claim" and Cell A2 contains the value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance". The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No Claim"
and "Claim" in the respective cells. However, when I change the formulas to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display "No
Claim" for all three cells. I expected to see the same results as generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[Peo Sjoblom]

I think you have misunderstood what ISLOGICAL does. It only tests if a cell
or a formula returns a Boolean value and returns TRUE if the cell/result is
TRUE or FALSE and returns FALSE for anything else.
Since FIND returns either a number or an error ISLOGICAL will always return
FALSE. In what way did you think it could help you where ISERROR could not?


--


Regards,


Peo Sjoblom


"EarlyBirdie" wrote in message
...
Cell A1 contains the value "Insurance Claim" and Cell A2 contains the
value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance".
The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No
Claim"
and "Claim" in the respective cells. However, when I change the formulas
to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display
"No
Claim" for all three cells. I expected to see the same results as
generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or
any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[Roger Govier[_3_]]

Hi

Find returns the starting position within the cell for the text being found,
1 in cell A1 and 11 in cell A3
If it is not found, then it will return a #VALUE error.

ISLOGICAL can only deal with values which return True or False

I don't understand why using ISERROR makes the formula too complex, compared
with ISLOGICAL
You could use ISNUMBER, but you would need to reverse your outcomes.
=IF(ISNUMBER(FIND("Claim",A1,1)),"Claim","No Claim")
--
Regards
Roger Govier

"EarlyBirdie" wrote in message
...
Cell A1 contains the value "Insurance Claim" and Cell A2 contains the
value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance".
The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No
Claim"
and "Claim" in the respective cells. However, when I change the formulas
to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display
"No
Claim" for all three cells. I expected to see the same results as
generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or
any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[EarlyBirdie]

OK, thanks everyone for your responses. So since using ISLOGICAL will not
provide the desired solution, let me provide more detail to what I am
attempting to do.

Cells in column A will contain text or contain nothing. In column B's
cells, I want to have a formula that checks the adjacent column A cell to see
if its text contains any of five specified six-character strings that may be
present anywhere in the text (there will not be more than one of the values
in each cell) and then display the six-character text that may be found or
indicate that none of the searched-for strings are present. I tried some
ISERROR combinations but can't find a suitable solution.

--
EarlyBirdie


"Roger Govier" wrote:

Hi

Find returns the starting position within the cell for the text being found,
1 in cell A1 and 11 in cell A3
If it is not found, then it will return a #VALUE error.

ISLOGICAL can only deal with values which return True or False

I don't understand why using ISERROR makes the formula too complex, compared
with ISLOGICAL
You could use ISNUMBER, but you would need to reverse your outcomes.
=IF(ISNUMBER(FIND("Claim",A1,1)),"Claim","No Claim")
--
Regards
Roger Govier

"EarlyBirdie" wrote in message
...
Cell A1 contains the value "Insurance Claim" and Cell A2 contains the
value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance".
The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No
Claim"
and "Claim" in the respective cells. However, when I change the formulas
to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display
"No
Claim" for all three cells. I expected to see the same results as
generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or
any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[EarlyBirdie]

OK,I figured out a solution using a series of IF(ISERROR formulas connected
as a string with "&". Thanks again for the explanation on the ISLOGICAL
function though. I always thought it was the opposite of ISERROR.
--
EarlyBirdie


"EarlyBirdie" wrote:

Cell A1 contains the value "Insurance Claim" and Cell A2 contains the value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance". The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No Claim"
and "Claim" in the respective cells. However, when I change the formulas to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display "No
Claim" for all three cells. I expected to see the same results as generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give the
desired results.


--
EarlyBirdie

[T. Valko]

Try this:

List the 5 text strings in a range of cells. Assume this range is F2:F6 -

F2 = string1
F3 = string2
F4 = string3
F5 = string4
F6 = string5

Enter this formula in B2 and copy down as needed:

=IF(A2="","",IF(ISNA(LOOKUP(2,1/SEARCH(F$2:F$6,A2),F$2:F$6)),"no
match",LOOKUP(2,1/SEARCH(F$2:F$6,A2),F$2:F$6)))

--
Biff
Microsoft Excel MVP


"EarlyBirdie" wrote in message
...
OK,I figured out a solution using a series of IF(ISERROR formulas
connected
as a string with "&". Thanks again for the explanation on the ISLOGICAL
function though. I always thought it was the opposite of ISERROR.
--
EarlyBirdie


"EarlyBirdie" wrote:

Cell A1 contains the value "Insurance Claim" and Cell A2 contains the
value
"Insurance Loss" and Cell A3 contains the value "Claim for Insurance".
The
adjacent cells in column B contain the formula
=IF(ISERROR(FIND("Claim",Ax,1)),"No Claim","Claim"), where "x" is the row
number and these formulas do return the expected results: "Claim", "No
Claim"
and "Claim" in the respective cells. However, when I change the formulas
to
read =IF(ISLOGICAL(FIND("Claim",Ax,1)),"Claim","No Claim"), they display
"No
Claim" for all three cells. I expected to see the same results as
generated
by the ISERROR formula. Any idea for correcting the ISLOGICAL formula or
any
explanation why these are the results? I have a more complex set of data
where using the ISERROR function makes the formula too complex to give
the
desired results.


--
EarlyBirdie

[Ron Rosenfeld]

On Wed, 4 Jun 2008 11:40:00 -0700, EarlyBirdie
wrote:

OK, thanks everyone for your responses. So since using ISLOGICAL will not
provide the desired solution, let me provide more detail to what I am
attempting to do.

Cells in column A will contain text or contain nothing. In column B's
cells, I want to have a formula that checks the adjacent column A cell to see
if its text contains any of five specified six-character strings that may be
present anywhere in the text (there will not be more than one of the values
in each cell) and then display the six-character text that may be found or
indicate that none of the searched-for strings are present. I tried some
ISERROR combinations but can't find a suitable solution.

List your desired strings in a range, and NAME the range "List" (or use the
actual cell reference).

Then try this formula to mimic your first example:

=IF(SUMPRODUCT(-ISNUMBER(FIND(List,A1)))=0,"No Claim","Claim")

Or, to display the actual code specified six-character string:

=INDEX(List,MATCH(TRUE,ISNUMBER(FIND(List,A1)),0))

entered as an **array formula** by holding down <ctrl<shift while you hit
<enter.

Formula #2 will give an error if there is no match, so perhaps, to combine the
two:

=IF(SUMPRODUCT(-ISNUMBER(FIND(List,A1)))=0,"No Claim",
INDEX(List,MATCH(TRUE,ISNUMBER(FIND(List,A1)),0)))

also entered as an **array formula**

--ron