# If cell A1 contains 'john' then cell B1 should equal 'red', if cell A2 contains 'paul' then cell V2 should equal 'blue'

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!

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)
``````
• Apologies but I have just changed my question slightly because the data does not always start with a name. Commented Oct 12, 2022 at 9:14
• Edited the answer. Please show realistic-looking sample data rather than gibberish. Commented 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 Commented Oct 12, 2022 at 10:17
• The formula I gave you works with the sample data you show. Commented 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. Commented Oct 12, 2022 at 10:35