Search for string within cell and return something useful

[melewie]

Hi All,

I am trying to run a check, what I have is a workbook with a list of all files in a directory the title of the files should contain an item number I also have a list of all Item numbers on a separate sheet within the workbook, I am really struggling to get what seems to be a simple formula working (i have never used these types of formulas before(poor excuse but its all I got))

So file name is on sheet 'Line balance Dir' column A and item code is on sheet 'Item Code' column A

been trying things along these lines =IFISNUMBER(SEARCH(A3,'Item Code'!A:A)) but not happening returns #name?

or =IF(FIND('Item Code'!A:A,A2),"TRUE","FALSE") returns #value!

any help is as always greatly appreciated

thank you

 

[mikerickson]

You can't use this with a full column reference for the Item Code, since "" (the blank cells at the end of the column) is in each cell.
A dynamic Named Range will solve that. Define a Name

Name: ItemCodes
RefersTo: ='Item Code'!$A$1 : INDEX('Item Code'!$A:$A, COUNTA('Item Code'!$A:$A), 1)

Then the formula =IF(SUMPRODUCT(--ISNUMBER(FIND(ItemCodes,A1)))=0, "none there", "Item code present")

 

[Andrew Poulsom]

For A1 on sheet 'Item Code':

=ISNUMBER(MATCH("*"&A1&"*",'Line balance Dir'!A:A,FALSE))

 

[melewie]

Thanks for your time gents, I realise now that I am a fool all seems so obvious now