This seems to work but need more testing
=SUM(IF(ISNUMBER(SEARCH(B1,Sheet5!M:M)),1,0))
Note it is an array formula so use Shift+Ctlr+Enter not just Enter to commit
it
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lisa" wrote in message
...
This is probably easy, but I'm having a heck of a time figuring out the
formula.

I have two spreadsheets with similar data. I am trying to figure out if
the
data in a cell (B2) on spreadsheet A (a value like "A452") exists at all
anywhere within a column (M) on spreadsheet B (where the cells read like
"A002, A452, A223"). Since the data can be anywhere in the cell, and
anywhere in the column, I can't seem to find a formula to work.

Help! Thanks!