Name manager doesnt' recognize full list

[morefrom Les]

I have created a name list using name manager and I know that the full list is included in the specific name. The problem is when I attempt to use the name in a formula only the name of the list is recognized and none of the other items on the list are recognized. To better explain I replaced one of the items on the list with ZZZZ and then entered that same data in the seal being searched. The result is a negative result.
I have used the meth of "use in forumula" option from the tool bar to ensure I was selecting a name list and still no success. I have used the formula before but this it is not working.

Specifically I am using =IF(ISNUMBER(SEARCH(XXXX,$O2)),"YES","NO") as my formula (XXXX is actually the name of the list). cell O2 contains a multiple words and is not a simple one word entry. I am attempting to get a yes or no result from the search of O2 for the names in XXXX list to identify whether any name from the list is contained within the cells content.

I have changed case of words in list, added spaces before after with no success. I have tried saving the file using multiple types of excel files with no change. I have used the control-shift-enter option when competing the formula but no success there either. I have even copied text from the box being searched into the name list and still no success. It acts as though the formula is only searching for the name of the list rather than what is contained in the list.

Thank you for any input.

[Pete_UK]

Try it like this:

=IF(COUNTIF(XXXX,"*"&$O2&"*"),"Yes","No")

Hope this helps.

Pete

[morefrom Les]

Thanks for the effort but that didn't resolve the issue. It returns #VALUE! using that that formula

[Pete_UK]

Do you have errors in the range defined by XXXX ?

Pete

[morefrom Les]

I don't have any errors. I have opened fresh workbooks and started from scratch incase I had something in conflict. I thought I had success until I realized the positive results only happen if I have first name in the name list in the box I'm searching. Since I also named my list by that same word I tried renaming the list to find out if the formula was searching for the first word or the word I used to name the list. When I changed the name of the list I returned a negative I have deleted and recreated the name list but it seems to only work when named the first name in the list and then selectively.

The only time it returns a positive result is when I have the first name on the list the same as the name of the list. Even when I use the tool bar and "use in formula".

What amazes me is that I had this formula working on another spreadsheet but it became corrupted. Now that I'm trying to rebuild it I can't make it work.

Example of success
Name list is name "Joe"
the list contains:
Joe
sam
peter

If I rename the list it continues to find only the first word on the list and no other words. This indicates to me I am using the list but for some reason name manager doesn't utilize the other names.
When I look in name manager it does however show the other items on the list as values to search for so those two things seem to be in conflict with each other.

I am certain this is user error and Im willing to plead ignorance but this is getting pretty frustrating. Spaces make no difference, case makes no difference.

[morefrom Les]

Let me redirect the conversation a little. Perhaps I'm using the wrong approach to the problem.

Is there another formula that will search a cell and compare it to content contained within a named range and return a yes/no response? I actually plan to rewrite the yes/no to display either blank or a desired title for the response. example in the list above I would label the response "Joe" for positive and blank for negative.

I hope I have not been trying to build an inappropriate formula for the intent,

The details of the project are:
I have a spread sheet about 3000 lines long. Each row has a common column that contains a paragraph or so of information and within that paragraph they consistently contain key words that can be used to filter the data down into common relationships. I need to search within a that box for each row to see which category to place the line for later filtering. I have 5 named ranges and I build a formula to the right of each rows content and then concatenate the collective blanks and positive responses into a single column. That way I can filter the rows down to only the relevant data. I wish the database was build differently but I'm using really old data and It's hard to go back and redesign what's been entered poorly in the past.

Any suggestions are appreciated.

[Pete_UK]

It would help if you attached a sample Excel workbook - this does not need to be the complete file, but the number of records should reflect the variations in your data and it should follow the same arrangement of data.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it does not work on this forum.

Hope this helps.

Pete

[Xiang Ho]

You may try an array formula for this

=OR(XXXX=O2) [Press Ctrl + Shift + Enter]

Now, if the value in O2 is in XXXX the result shall be "True", Else it would be shown as "False".

If you insist it to be shown as "Yes/No" instead of "True/False", then you can write it as

=IF(OR(XXXX=O2)=TRUE,"Yes","No") [Press Ctrl + Shift + Enter]

[morefrom Les]

Thank you all for your responses. I have to think I have a problem on my end with the file I'm using. I have found a way around the problem that is actually much simpler than using the formula so Ill use that method.

I will mark the problem resolved as I am certain your recommends should have fixed the issue under other circumstances.

Thank you again.