Search in Cells from a list of values

[BrandonCarder]

Hello,

I have a column in spreadsheet 1 of about 700 values (part numbers). I am wanting to search several columns in spreadsheet 2 that might have the part number from spreadsheet 1 within the range of cells that could be an exact match or within a cell that contains the match (ie it could be in the cell but contain other words in the same cell).

So i am basically wanting the formula to search within each cell of the data range chosen and let me know if it finds a match. If it finds a match im wanting it to return the matched part number it found within the data range of cells in the row. I'm confident it will only find one match in the row from my original list of 700.

From researching it seams like it needs to be a combination of an If statement and something else utilizing a list and a search within a cell type function.

Any help greatly appreciated!

 

[BrandonCarder]

I came close from this link http://ccm.net/forum/affich-46323-find-word-within-cell-put-value-in-adjacent

with =IF(ISERROR(SEARCH("*apple*",A1,1)),"","A ") However my list is huge and i believe this is only search one column