Searching for a String of Text within a Range and returning an Adjacent Value

[samimust]

Hi,

I need a formula for the below:

I have a cell that contains a range of data, but more importantly a street name, with a reference in the adjacent cell.

I have a report which has a street name on it's own. I need to see if that Street Name is present within the cell on the first tab, and then return the adjacent cell that contains the reference.

I have attached a sample sheet.

Please help.

Thanks,

Sam

[davesexcel]

Possibly this,

=IFERROR(VLOOKUP("*" & A2 &"*",'Report 1'!$A$2:$B$12,2,FALSE),"Not Found")