How/Can I do this Formula in Excel 2007?

[BKearan]

Trying to compare part of a cell to a column of text.

=IF(ISNUMBER(SEARCH(Sheet1!B159:B192,E3)),"Yes","No")

The above does not work, but I think it gets the intent across. If I replace "Sheet1!B159:B192" with "jim" and E3 contains "Jack Jimbo" I would get a "Yes". I don't want to limit the text to just "Jim" I want to compare each cell in b159 thru b192 ( Which, lets say comma separates cells, may be : "Jack,Jim,Bo,Richard,Chris" )

Does that make sense? If so, Can it be done in Excel 2007? And if so, How?

 

[SkipVought]

Why are you using ISNUMBER if you're searching for TEXT?

Is the cell Value Jack or is that PART of the cell value?

Check out the MATCH function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BKearan]

From the Excel Help:

Check if part of a cell matches specific text
To do this task, use the IF, SEARCH and ISNUMBER functions.

 Note   The SEARCH function is case-insensitive. 
   
Formula Description (Result) 
=IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") Checks to see if A2 contain the letter v (OK) 
=ISNUMBER(SEARCH("BD",A3)) Checks to see if A3 contains BD (TRUE)

In short, I'm using "ISNUMBER" because that is what Microsoft said to do. (and I don't know any better)

Your second question didn't make much sense.
I'll try to clarify my question :
The value of cell E3 is "Jack Jimbo"
If I use =IF(ISNUMBER(SEARCH("Jim",E3)),"Yes","No") as the formula, I get a Yes - which is good.
I do not want to compare cell E3 to "Jim", I want to compare cell E3 to a whole Range of cells - Sheet1!B159:B192 - to see if any of those cells match a part of cell E3's value.
b159 = "timmy"
b160 = "Joe Jim"
b161 = "buck"
b162 = "Larry"

I want the formula to tell me that the Value of E3 ("Jim") was found in Sheet1!B159:B192 (a part of b160 is "Jim")
or the reverse, one of the cells in the range contains a part of the value in E3.

 

[BKearan]

I want the formula to tell me that the Value of E3 ("Jim") was found in Sheet1!B159:B192 (a part of b160 is "Jim")
or the reverse, one of the cells in the range contains a part of the value in E3.

Doah! That should be :

I want the formula to tell me that a cell in Sheet1!B159:B192 contains a part of the Value of E3 (If a part of b160 is "Jim", or the value of b168 is "Jim", the result is "YES")

I would suppose that ISNUMBER is used because search returns the number of the character the string is found in. So if it returns a number, then the string was found, but, if it returns "null" the string was not found.

I just want to know if it is possible to put a Range of cells to check instead of just one static string of text.

 

[SkipVought]

The SEARCH function...

SEARCH, SEARCHB

SEARCH and SEARCHB locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
...
SEARCH(find_text,within_text,start_num)

You might try using Find within your range from the Edit menu.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BKearan]

This works:

=OR(ISNUMBER(SEARCH(Sheet1!B159:B192,E6)))

IF you set the formula to an "array" formula by selecting the cell, press "F2" then press "ctrl+shift+enter"

It tells me that, yes, at least one cell in b159 thru b192 had a value that was somewhere in the value of cell E6.