If column contains cell text

[Prcntrygrl]

I am trying to return a "yes" if a column contains text in a referenced cell.

ie A1 contains the word red. Somewhere in column B a cell has the word red, so it should return a yes. however, this isn't working, I can only get it to work if I type the word "red" in the formula. Unfortunately, my actual spreadsheet is a list of about 900 names and I don't want to type each one.

Please help.

[FDibbins]

=if(B1=$A$1,"yes","no")

[Prcntrygrl]

I know how to find if two cells equal each other. What I'm am trying to do is find if it is somewhere in the 899 rows in a column.

So, I have 2 databases that can each contain up to 899 employees. I am trying to find out if all the names on one are on the other. So, i need to return if a cell in the entire column contains the information.

This formula works:
=IF(ISNUMBER(SEARCH("name goes here",B1:B899)),"yes","no")

but only if I actually type the name into the "name goes here". i would instead like to reference a cell that contains that name as reference.

[FDibbins]

Post # 3 was far more descriptive, thank you

I resume you have tried...
=IF(ISNUMBER(SEARCH($A$1,B1:B899)),"yes","no")

[Prcntrygrl]

Yes, I have and it always returns no. I even tried the "*"&A1&"*" and it still wouldn't find it.

[FDibbins]

can you upload a sample workbook (no sensitive data), showing what you are working with, and what your expected outcome would be?

[Prcntrygrl]

Okay. I actually figured it out on my own.

thanks!!

[FDibbins]

Great, its always a good feeling when you resolve your own questions

Would you mind sharing what you did? That way, if another member searches for something similar, your answer will help them

[Prcntrygrl]

This is the formula I used.

=IF(MATCH("*"&P2&"*",Sheet5!$N$2:$N$1000,0),"Yes","no")


P2 being the cell containing the name and Sheet5!$N$2:$N$1000 containing the other list of names.

[FDibbins]

Fantastic, thank you very much