Search for Text within the TEXT in a cell and return that cell if found

Question

I have browsed internet for examples but cant put it all together to work.

I am looking to search for a case insensitive text within a range of cells and if found bring back the full value of the cell that contains that text.

As per my image above i would like to reference column A, look for it in Range C:C, and if match is found return the full value of that Cell in column B.

Answer 1

So here is your formula, simple VLOOKUP with asterisks(*) to make it a wildcard:

=VLOOKUP("*"&A2&"*",$C$2:$C$8,1,FALSE)

Answer 2

Try this formula in B2 and autofill down.

Remember to use CTRL + SHIFT + ENTER when inputing this formula.

=INDEX($C$2:$C$8,MATCH(TRUE,ISNUMBER(SEARCH(A2,$C$2:$C$8)),0))

Answer 3

It took me some time, since this is my first time answering on here. Hope this helps you get further to your solution:

=IF(FIND(A2,C2)=1,CELL("contents",B2))