If cell A1 contains the string 'john' then cell B1 should return 'red', if cell A2 contains the string 'paul' then cell V2 should return 'blue'

Question

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

Name	Colour
Paul	blue
John	red

I'm looking to create a formula in the 2nd column below that will return the colour associated with the name if the 1st column contains the name anywhere in the string.

Data	Colour
Paul*6871628	blue
paul*6871628	blue
apda23.zzJohn	red
asdazjohn	red
borp paul reck	blue
jgk John wqty	red

Answer 1

Use search() and filter(), like this:

=lambda( 
  searchKeys, names, colors, 
  map( 
    searchKeys, 
    lambda( 
      searchKey, 
      iferror( single( filter(colors, search(names, searchKey)) ) ) 
    ) 
  ) 
)(A2:A, Names!A2:A, Names!B2:B)

The formula is a bit on the heavy side, so you may want to remove any unneeded blank rows at the bottom of the data and search tabs.