Finding multiple words up to 10 different columns

[MikeB41]

Hi All,
I would appreciate any help with the following:
I have an excel worksheet with approximately 650 rows by 77 columns wide and need to search for approximately 9 columns of key words and then in the 10th which contains problem text I am interested in that has now been qualified by the previous 9 columns on a given row and then create a new worksheet with the 10th text cell containing the word of interest.

I have used some code that was created by mdmackillop # 780 id=780

it works great however it only searches for a single word, when I need it to search for 10 words an the last 10th it returns that cell on the new sheet that was created.

I guess if you could picture using an AutoFilter to search each column (up to 9 columns) that contains the required word in each and when you get to the 10th column searching for the final word.

Thank you,

MikeB41

[Bob Phillips]

Sorry mate, I am a tad confused by that.

Can you post a sample workbook, with an example or two?

[MikeB41]

Hi kb,

Thank you for your quick response. I will have to create a sample workbook the one I have contains (77 columns wide) of proprietary information. I will try and construct a smaller one with some generic names/words early this afternoon; I have a meeting I am leaving for now.

thank you,

MikeB41

[MikeB41]

Sorry mate, I am a tad confused by that.

Can you post a sample workbook, with an example or two?

Hi kb,

I don't know if this will help but here's a sample workbook:

[Bob Phillips]

Try this ARRAY formula

=INDEX(A:A,MATCH(1,($B$2:$B$16=$B21)*($C$2:$C$16=$B22)*($E$2:$E$16=$B23)*($ F$2:$F$16=$B24)*($G$2:$G$16=$B25)*($H$2:$H$16=$B26)*($I$2:$I$16=$B27)*($J$2 :$J$16=$B28)*(LEFT($K$2:$K$16,LEN($B29))=$B29),0)+1)

and copy across

[MikeB41]

Hi XLD,

Thank you for the array formula, it works great; however Iwas hoping to be able (I'm not that proficient yet) to contruct a search Userform/combobox to contain the terms i want to search for and insert this code into the code created by "mdmackillop" from this site and replace his userform with a new expanded one and then execute his code to do the balance of what it does. I have attached a copy of his code which also contains sample data; his code only searched one column when I need to search multiple columns. I would appreciate your thoughts, am I reaching too much.

Thank you,

MikeB41