Let's say we have a table like below which pairs each name with a colour:

|Name|Colour|
|:---|:-----|
|Paul|blue  |
|John|red   |

I would like a formula in the 2nd column below that will say: if cell contains 'Paul' then colour is 'blue' etc.

|Data      |Colour|
|:---------|:-----|
|52!Paul*22342|   ?  |
|John/23rff|   ?  |

Any help would be much appreciated!