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!

1 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:

  names, namesAndColors, 
    arrayformula( regexextract(names, "[A-z]+") ), 
      iferror( vlookup(name, namesAndColors, columns(namesAndColors), false) ) 
)(A2:A, Names!A2:B)
  • Apologies but I have just changed my question slightly because the data does not always start with a name.
    – fini-stair
    Oct 12, 2022 at 9:14
  • Edited the answer. Please show realistic-looking sample data rather than gibberish. Oct 12, 2022 at 9:23
  • Thank you very much for your help. Your answer works for me as long as the name is at the beginning of the string. Is there a way to make it work if the name is contained anywhere in the string? In response to your comment: my question is representative of the data I'm working with, which is like gibberish
    – fini-stair
    Oct 12, 2022 at 10:17
  • The formula I gave you works with the sample data you show. Oct 12, 2022 at 10:30
  • If your real data is considerably different from what you show in the question, you may need a completely different solution. If that is the case, please post a new question with more realistic-looking sample data. Oct 12, 2022 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.