Search for specific value in range of cells

[AlexDignan]

I'm stumped trying to get my formula to do what I want it to do. The formula I am using is:

=IF(ISNUMBER(SEARCH("2003",F4)),"NOT OK","OK")

and the goal is to search for the text 2003 in a cell. This formula works perfect for cell F4 when the text is something like: "Microsoft Windows Server 2003 R2 Enterprise".

What I need to edit this formula to do, is to search not only F4, but ALL cells in column F to see if any of them contain 2003. My first thought was to edit the formula to:

=IF(ISNUMBER(SEARCH("2003",F:F)),"NOT OK","OK")

, but the evaluation changes the range from F:F to 0, which always returns true, no matter what the text box has.

I basically need a formula that will search a column, and if the text "2003" appears, I need it to spit back "NOT OK", if it does not contain 2003, it should return "OK".

 

[Joe4]

Welcome to the Board!

Try:

=IF(ISNUMBER(MATCH("*2003*",F:F,0)),"NOT OK","OK")

 

[JoeMo]

This should work to find 2003 as text embedded in a string, but not as a standalone number:

=IF(COUNTIF(F:F,"*2003*"),"NOT OK","OK")

 

[Aladin Akyurek]

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(2003,F:F))),"NOT OK","OK")

This returns NOT OK as long as there is at least one cell containing 2003, otherwise OK.

 

[AlexDignan]

This should work to find 2003 as text embedded in a string, but not as a standalone number:

=IF(COUNTIF(F:F,"*2003*"),"NOT OK","OK")

What do you mean by "standalone number"? if I have 2003 as a general type will this still work, or does it have to have text included with it (ie: ABC 2003)?

 

[AlexDignan]

Thanks!

This does work as long as the 2003 is accompanied by at least one letter.. I will check the data to make sure it will work!

 

[Aladin Akyurek]

Thanks!

This does work as long as the 2003 is accompanied by at least one letter.. I will check the data to make sure it will work!

Did you also try the suggestion in post #4?

 

[JoeMo]

What do you mean by "standalone number"? if I have 2003 as a general type will this still work, or does it have to have text included with it (ie: ABC 2003)?

If you enter 2003 as a number in a cell in col F, with no other cell containing 2003 in that column, the formula will return "OK". If instead you enter '2003 (a prefix apostrophe) to make this text, then the formula sees it and returns "NOT OK".

 

[AlexDignan]

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(2003,F:F))),"NOT OK","OK")

This returns NOT OK as long as there is at least one cell containing 2003, otherwise OK.

This is a better solution, as it does work on fields that only contain 2003, with no other accompanying characters. If you can, could you explain what the 9.999E+307 is doing?
Thanks

 

[Aladin Akyurek]

This is a better solution, as it does work on fields that only contain 2003, with no other accompanying characters. If you can, could you explain what the 9.999E+307 is doing?
Thanks

1) 9.99999999999999E+307 is a constant of Excel itself, expressing a limit value of the software.

2) LOOKUP and kindred functions ignore (skip) error values in the reference they look at unless if they cannot do otherwise.

See: http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

3) Given the BigNum in [1] as the value to seek, LOOKUP will always return the last numeric value in the reference it looks at if such available or #N/A if it cannot.

4) SEARCH returns either an error (#VALUE!) or a position (i.e. an integer number) when successful.

5) LOOKUP carrying BigNum picks up the last position SEARCH returns if available or #N/A.

6) ISNUMBER returns TRUE if [5] ends up in a position, FALSE otherwise.

7) The IF maps TRUE to NOT OK, FALSE to OK.

Hope this helps.