Index and Match text when the matching data has additional information

[colindg]

I have a list of several hundred buildings from which I want to index several other columns of data from another lists on other sheets. The building names have had additional data added. for example List one "23 River Street" and on the second "23 River Street - 5678" I have tried =IFERROR(INDEX(Kern!$A$2:$A$122,MATCH(C2,Kern!$B$2:$B$122&"*",0)),"TBC") but this does not work.

Any ideas?

 

[Weazel]

assuming there aren't going to be any duplicates you could try something like.....

=IFERROR(LOOKUP(9.99E+307,SEARCH(C2,Kern!$B$2:$B$122),Kern!$A$2:$A$122),"TBC")

 

[colindg]

assuming there aren't going to be any duplicates you could try something like.....

=IFERROR(LOOKUP(9.99E+307,SEARCH(C2,Kern!$B$2:$B$122),Kern!$A$2:$A$122),"TBC")

Thanks that works as you expected now I have to deal with the spelling mistakes in my lists which is not so bad.