vlook up type macro help

[Tonyk1051]

Sample 1.xlsx
the sample spreadsheet already has the work done but it took quite a while
column headers all the same but the amount of data always different so the macro has to work for any amount of data
here are the rules applied, some MFRs have multiple references some dont
when its all said and done the last thing to add to the macro would be is to have cells that have something in column H, cut and paste to sheet 4
hope someone can help, thanks

 

[Tonyk1051]

ok not vlook up but more of a IF and condtional macro, if multiple coulmns have certain words then i coulmn H write this word, etc