If cell A1 contains 'john' then cell B1 should equal 'red', if cell A2 contains 'paul' then cell V2 should equal '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 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!

Answer 1

Assuming that the lookup table of names and colors is in Names!A2:B, and that the text strings in the Data column always start with an name consisting of purely alphabetic characters A-Z, with optional leading or trailing non-alphabetic characters, you can use regexextract() and vlookup(), like this:

=lambda( 
  names, namesAndColors, 
  map( 
    arrayformula( regexextract(names, "[A-z]+") ), 
    lambda( 
      name, 
      iferror( vlookup(name, namesAndColors, columns(namesAndColors), false) ) 
    ) 
  ) 
)(A2:A, Names!A2:B)