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!

=COUNTIF([b.xls]Sheet1!M:M,"*"&B2&"*")

Change b.xls to spreadsheet b' actual name, and sheet1 to the sheet name.

The formula will return 0 if not found, or the number of times it is found. Note that A452 would be
found in the string A4526, so you may not be able to use this formula if that is the actual case.
If your values are always letter number number number, then it will work.

HTH,
Bernie
MS Excel MVP


"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!

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!

Thank you thank you! That works!

"Bernie Deitrick" wrote:

=COUNTIF([b.xls]Sheet1!M:M,"*"&B2&"*")

Change b.xls to spreadsheet b' actual name, and sheet1 to the sheet name.

The formula will return 0 if not found, or the number of times it is found. Note that A452 would be
found in the string A4526, so you may not be able to use this formula if that is the actual case.
If your values are always letter number number number, then it will work.

HTH,
Bernie
MS Excel MVP


"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!

Hi,

Try this in cell C2 of sheet A

=if(countif(sheet2!M2:M100,B2)=1,"Found","Not found")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"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!