Find string of text within a cell and then replace the cell with new text

Friends,

So, here's the situation:

I have cells containing such info:

123-ABC-321
99999 nam 7
---, - jane 000

How can I lookup, for example, ABC, then based on it, replace the whole cell into a new cell to something like: Anderson?
Another example, for, nam, look up, nam, then replace the whole cell to a new cell to something like: Jason.

Am I making sense?

Please let me know if I need to explain or attach a file for better comprehension.

It's really that much I needed. To simply look up a string of text within a cell then replace the whole cell into something anything I would like it to be.

I hope this helps.

Column A has unique items.

Column B shows names that will need to be looked up.

Column C will be the text the whole Column A cells are to be fully replaced with.

Originally Posted by Glenn Kennedy

I interpreted this differently...


=INDEX($C$2:$C$5,SUMPRODUCT(IF(--ISNUMBER(SEARCH($B$2:$B$5,A2)),ROW($C$2:$C$5)))-ROW($C$2)+1)

This!

Thank you very much!

Absolutely, perfect!

Thank you very much for your time and support.

I was actually looking for what Glenn Kennedy has provided.

But thank you, really.