Search for String in Range, If Match, Return Matching Cell Value

I'm having a dog of a time with a formula that will search for string in a range and if a match is found, then return the contents of the cell that matches. For instance in cell B1 if I am searching A1:A3 for the word "Red" then in B1 it would return "Red Apple" since red was found in A2. See small example below:

Column A Column B

Blueberry Red Apple
Red Apple
Green Apple

Hi,

Try the following array formula on your data set. It works for the sample data, you will just need to adjust the ranges to match your actual data:

Formula:

Please Login or Register  to view this content.

Remember, this is an array formula, so you will need to use Ctrl + Shift + Enter to confirm it instead of just Enter.

Hope this helps

Thanks Fotis, I wasn't aware of the use of a wildcard (*) in Excel formulas. I learnt something today!

Originally Posted by Fotis1991

Put the word that you are looking for in a cell-let's say in G1 and then use.

=INDEX(A:A,MATCH("*"&G1&"*",A:A,0))

Worked like an absolute charm...I actually needed to look for a specific string length of characters in multiple cells so I altered it a bit and pulled it down to several cells...if anyone is looking for another solution based on specific characters in a cell here is what I came up with thanks to Fotis1991:

=INDEX(GL$7:GL$2500,MATCH("*"&LEFT(D12,LEN(D12)-7)&"*",GL$7:GL$2500,0))

Thanks everyone!