checking if value exists inside a cell

[Terrible]

Hi,

I've got a list of 2000 items in sheet1 col A and in another sheet I've cut and pasted the contents of a text file there (sheet2). What I'd like to do is to check whether the items in the list are present in that file. I've tried using IF(COUNTIF etc.) but the problem is that it checks to see if the value of the cell matches a cell in sheet2.

In this case the value from the first sheet will not exactly match what is in the second sheet because there will be lots of other data in the same cell.

So for example in my list in col A I have the value BA01 and in the second sheet, there will be a cell which contains the text:-
932 JOB 00:00 - - A UKDOSVS FTP UK-REPS FTBA01AC FTBA01AC 02/10/2012 00:15:50

In this case I'd like BA01 in the first sheet to be have a one associated to it (or a "Y") but using IF(countif() doesn't seem to work unless the cell only contains "BA01".

What am I doing wrong?

TIA

Tony

 

[Scott Huish]

If you want a 1 or 0, then this should work:

=(ISNUMBER(MATCH("*"&A1&"*",Sheet2!A:A,0)))+0

Excel Workbook
	A	B
1	BA01	1
Sheet1

 

[Terrible]

Wa-hey! Fantastic it worked a treat!

Thanks

 

[TheCman81]

Excel 2010
	A	B
1	BA01	Yes
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ISERROR(VLOOKUP("*"&A1&"*",Sheet2!A:A,1,0)),"","Yes")