Index and match if field contains specific text in it

[saintsman26]

Hi - hope this is clear, what I want to do is look up cell content
(text) and if that word appears in another table return a valu
associated with that cell
ie
ColA Col B
London Borough of Croydon 109
London Borough of Ealing 110
London Borough of Enfield 104
London Borough of Greenwich 109
London Borough of Hackney 112
London Borough of Hammersmith and Fulham 115

Col C ColD (what I want!)
Croydon 109
Ealing 110
etc
Hammersmith and Fulham 115

The data comes from an external source so I am stuck with that

Any ideas (prefer to use formula rather than vba)
Thank you in anticipatio

 

[Claus Busch]

Hi,

Am Thu, 29 Nov 2012 18:11:37 +0000 schrieb saintsman26:

ColA Col B
London Borough of Croydon 109
London Borough of Ealing 110
London Borough of Enfield 104
London Borough of Greenwich 109
London Borough of Hackney 112
London Borough of Hammersmith and Fulham 115

Col C ColD (what I want!)
Croydon 109
Ealing 110
etc
Hammersmith and Fulham 115

in D1 try:
=INDEX(B1:B100,MATCH("*"&C1,A1:A100,0))


Regards
Claus Busch