excel - Solution to Find string from a list , to another string VBA -

what is, have excel load every day football, tennis

[daily list] : https://www.dropbox.com/s/45ulku7val6q6lj/sample.jpg?dl=0

additionally have excel file/sheet name of tennis players, football teams names, football leagues in format :

[redlist] : https://www.dropbox.com/s/h9nqx2zpq696o38/redlist.jpg

of course can change format of list test list.

now want check second list (red list) if of teams names appear on main sheet names , highlight row, facing issues additional parameters .

one way tried sumproduct function :

=if(sumproduct(countif(a4,"*"&$f$2:$f$6&"*"))=0,"none found",index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6,a4)),),0))) 

which works fine not working cause instance if have in red list name yankel zemel , , in daily list name appears yankel z, or zemel yankel, or zemel y. , function return false.

another issue comma between 2 names or special characters anif / salzburg ii , askÖ gmünd, blau-weiß linz ... etc

so tried figure out how solve problem.

one solution use find in vba again im not sure if work. solution little bit more tricky create access database , try store there different versions of each name, instance zemel yankel = yankel zemel = yankel z = zemel y. = yankel, zemel etc rename them unique name instance zemel yankel. truth dont know if easy dont have lot of knowledge in access , dont know if solution too.

another solution have no idea again it, use regexp

did ask ? how solve problem ?

like tom mentioned not easy problem slight twist can achieve want. did similar project yours employed method , client pretty happy arrangement.

create new sheet called, say, alias , maintain table there. this. whenever come across alias, put in table. these football games, not come across more 10 aliases (as far have seen till date). if come across more names update table.

now use .find loop through aliases find match.

you can try , evaluate formula in vba code when looping through aliases. example

dim rngalias range  '~~> hard coding. loop through range set rngalias = thisworkbook.sheets("alias").range("a2")  'sformula = "=if(sumproduct(countif(" & _            "a4" & _            ",""*""&$f$2:$f$6&""*""))=0,""none found""," & _            "index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6," & _            "a4" & _            ")),),0)))"  sformula = "=if(sumproduct(countif(" & _            rngalias.address & _            ",""*""&$f$2:$f$6&""*""))=0,""none found""," & _            "index($f$2:$f$6,match(true,index(isnumber(search($f$2:$f$6," & _            rngalias.address & _            ")),),0)))" 

hope gets started!