nested find in if problem

  • Thread starter Thread starter gs
  • Start date Start date
G

gs

for excel 2003, the formula
=IF(FIND("s",A2:A14),B2,IF(FIND("s",A2:A14),C2,0)) will fail ( show #VALUE
as result) when s is not present but "r" is.

what can one do about that?
 
Try this:
=IF(COUNTIF(A2:A14,"S"),B2,IF(COUNTIF(A2:A14,"R"),C2,0))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
You have to look for the error first
Something like this IF(ISERROR(FIND("s",A2:A14),C2,B2)
but.....find will not work on a range, only on a cell.
If you want:
If there is a cell with a sinlge "s" in A2:A14
then give me B2
esle give me C2
Then =IF(COUNTIF(A1:A14,"s"),B2,C2) will do

If you want to find a "s" within a string in any cell in the range, the
problem is more diffiuclt.
Which do you want?
best wishes
Bernard
 
pardon me, actually I don't need range, I goofed in the OP

=IF(FIND("s",A1),B1,FIND("r",A1)*C1)

should have been what I tried to determine the result value: if a cell has a
letter "s", I want value in B1, "s" in C1, otherwise D1

The original problem was slightly more complex ( different formula
depending on what is found first)


Countif will not work as I am looking if a cell has certain letter
 
great, thank you, got it working

=IF(ISERROR(FIND("s",A1)),IF(ISERROR(FIND("r",A1)),D1,C1),B1)

a bit twisted, not as straight forward in conventional sense but works


would have been nice if find("whatever", a1)> 0 will either be true or
false instead of error
 
Back
Top