# 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'

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
• This is doable in Google Sheets, and probably also in Microsoft Excel, but it is likely that no single solution can cover both platforms. If your question is regarding Google Sheets, please delete the `microsoft-excel` tag. Commented Oct 12, 2022 at 13:58
• I removed the excel tag. Commented Oct 12, 2022 at 16:18

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.

• Brilliant. Thank you so much. Really appreciate it! Commented Oct 12, 2022 at 14:33
• I'm curious to know why you went for this approach when if we use the formula below which is at the core of your solution we seem to get the same result? "=iferror( single( filter(colors, search(names, searchKey)) ) ) : Commented Oct 12, 2022 at 16:19
• The formula you quote needs to be copied down. You can make it work like this: `=iferror( single( filter(Names!B\$2:B, search(Names!A\$2:A, A2)) ) )`. The formula in the answer is an array formula that fills the whole column automatically, which makes spreadsheet management easier by avoiding one-off typos and the like. Commented Oct 12, 2022 at 17:36
• thanks so much! this is super helpful. Commented Oct 12, 2022 at 20:07