Search a cell value that contains a text in a table

[mhuddles1981]

Hi,

I'm not sure if this is possible with a worksheet function or not. I have a large table on Sheet 1 with 2 columns minimum A (Type) B (description) and a a table on sheet 2 with 3 columns A(Type Name), B(QTY), C(Value) and variable number of rows.
What I am looking to do in each cell in column A on sheet 1, I need a formula which will search Sheet 1 - B(description) to see if contains ANY text from the table on Sheet 2 under column A(Name Type) if it does contain the text to return the text as the value for Sheet 1 Column A.

I hope that made sense.

Please note the text in the table is variable length and not always in the same part of the description.

Any help is appreciated.

Regards

Michael

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[mhuddles1981]

Hi Davesexcel,

Thanks for the tip here is a dummy spreadsheet. Sheet 1 and 2 are as I mention. The last sheet RESULTS is the type of result I am Chasing.

I hope I makes sense with that I am looking for.

Cheers

Mhuddles1981

[davesexcel]

Maybe this example will work, click the button the results will be ion the Results Sheet

[mhuddles1981]

hi Davexcel,
I like the VBA and it work great in another project of mine, however in this particular aspect I need the result to populate in sheet1 column A. The data that is in sheet is usally about 10,000 to 30,000 lines down and is really indepth. Also this data gets copied from another workbook everyweek.
I was hoping that there would be a formula I could use in cell that would give me the result I need.

I'll try to fiddle around with your code in the actual workbook to see what i can do to populate it in the same sheet.

Thanks for your help, if you have any other suggestions I would certainly like to hear them

Cheers