Finding info within a data set

[Lisa]

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!

 

[Bernie Deitrick]

=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

 

[Bernard Liengme]

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

 

[Lisa]

Thank you thank you! That works!

=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

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!

 

[Ashish Mathur]

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