search text of cell in a range and return the contents in the corresponding column

Hi all
I am trying to look for a specific word within the text in a range of cells and result should be the value in the corresponding column.
example.
Look for CIBC and result should be "4km"
Bank TD 2km
RBC Bank 3km
CIBC Bank 4km
Scotial Bank 5km
National Bank 6km
Bank of America 7km
HBL Bank 8km
HSBC Bank 9km

Many Thanks

Thank you so much for your help. It worked.
Regards

No problem

You can change the post settings to solved if you feel your problem is solved.

Press the * under my name to give rep if I helped you! Ta.

Done... Sorry I am new to the forum so took a lil bit longer to mark it solved.

I have another question if you can help. I will post a thread for the same as well.

There are following 4 columns. In another sheet I need summary of only those names who have to be followed up (no blank spaces between rows)

Name Ph Address Followup
S Bano (123) 456-7890 Erin Center Blvd y
J Maca (123) 456-7890 Erin Center Blvd
R Ali (123) 456-7890 Erin Center Blvd
A Ona (123) 456-7890 Erin Center Blvd y
A Lopez (123) 456-7890 Erin Center Blvd
T Amrani (123) 456-7890 Erin Center Blvd
G Elmido (123) 456-7890 Erin Center Blvd y
Rogowski (123) 456-7890 Erin Center Blvd
E Sajol (123) 456-7890 Erin Center Blvd

Thanks in advance.
Kumail