CF with this formula

[glerwell]

Hi

I have this forumula:

=IFERROR(IF(R13="",R14&" "&MATCH(FALSE,INDEX(R14=R14:R47,0),0)-1,R13&" "&MATCH(FALSE,INDEX(R13=R13:R47,0),0)-1),"")

This returns a current sequence, the only sequences it will return would be WON #, LOST #, BB-WON # and BB-LOST #.

# is the current number sequence, ie. Won 3 or BB-LOST 2.

What I want is for the CF to be activated if "LOST" is returned in anyway.

So if the formula returned "LOST 4" or "BB-LOST 2" then the CF would kick-in, and change the font red.

I am guessing that I would have to use a formula to achieve this CF.

Any help would be grateful!

Thanks

 

[glerwell]

Sorted it now ith this formula:

=NOT(ISERR(SEARCH("LOST",$AC$44)))

 

[jasonb75]

If I understood that correctly,

=FIND("LOST",IF(R13="",R14&" "&MATCH(FALSE,INDEX(R14=R14:R47,0),0)-1,R13&" "&MATCH(FALSE,INDEX(R13=R13:R47,0),0)-1))

Finding LOST in the result should return a number which conditional format willl interpret as TRUE, anything else will return an error which will interpret FALSE.


edit: thought you wanted the lookup in the CF, my mistake.

You could get away with

=SEARCH("LOST",$AC$44)

using ISERR to return FALSE from the search, then NOT to reverse the logic is not needed, as I mentioned above, numeric results are interpreted as TRUE, errors as FALSE.